6 Replies Latest reply: Nov 17, 2012 6:28 AM by ranit B RSS

    Automatic Item code

    only.ashish99
      Hi,

      I want to generate an automatic item code (created by adding two or more columns).

      e.g PO_no ID Po_type Company Item

      LPO001 001 LPO ABC XYZ
      IPO002 002 IPO ABC XYZ

      po_no= (PO_type+ID)

      Please help, thanks
        • 1. Re: Automatic Item code
          Solomon Yakobson
          PO_type || ID
          SY.
          • 2. Re: Automatic Item code
            only.ashish99
            Sorry i mentioned incorrect.

            PO_no ID Po_type Company Item
            LPO001 001 LPORD ABC XYZ
            IPO002 002 IPORD ABC XYZ

            how to trim this RD?
            • 3. Re: Automatic Item code
              Hoek
              Depending on your unknown/unmentioned database version, you could opt for a virtual column:
              SQL> create table t as 
                2  select '001' id, 'LPO' po_type, 'ABC' company, 'XYZ' item from dual union
                3  select '002', 'IPO', 'ABC', 'XYZ' from dual;
              
              Table created.
              
              SQL> select * from t;
              
              ID  PO_ COM ITE
              --- --- --- ---
              001 LPO ABC XYZ
              002 IPO ABC XYZ
              
              2 rows selected.
              
              SQL> alter table t add po_no varchar2(10) generated always as (po_type||id) virtual;
              
              Table altered.
              
              SQL> select * from t;
              
              ID  PO_ COM ITE PO_NO
              --- --- --- --- ----------
              001 LPO ABC XYZ LPO001
              002 IPO ABC XYZ IPO002
              
              2 rows selected.
              http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php
              If you're not on database version 11.1 or onwards, you could use a trigger that would insert the po_no on insert.


              See the SQL and PL/SQL FAQ if you do not know your database version or how to obtain it.
              The FAQ can be found here: https://forums.oracle.com/forums/ann.jspa?annID=1535
              Specifically:
              {message:id=9360002}
              • 4. Re: Automatic Item code
                Solomon Yakobson
                only.ashish99 wrote:

                how to trim this RD?
                SUBSTR(PO_type,1,3) || ID
                SY.
                • 5. Re: Automatic Item code
                  Hoek
                  how to trim this RD?
                  REPLACE could to that:
                  SQL> create table t as 
                    2  select '001' id, 'LPORD' po_type, 'ABC' company, 'XYZ' item from dual union
                    3  select '002', 'IPORD', 'ABC', 'XYZ' from dual;
                  
                  Table created.
                  
                  SQL> select * from t;
                  
                  ID  PO_TY COM ITE
                  --- ----- --- ---
                  001 LPORD ABC XYZ
                  002 IPORD ABC XYZ
                  
                  2 rows selected.
                  
                  SQL> alter table t add po_no varchar2(10) generated always as (replace(po_type, 'RD')||id) virtual;
                  
                  Table altered.
                  
                  SQL> select * from t;
                  
                  ID  PO_TY COM ITE PO_NO
                  --- ----- --- --- ----------
                  001 LPORD ABC XYZ LPO001
                  002 IPORD ABC XYZ IPO002
                  
                  2 rows selected.
                  • 6. Re: Automatic Item code
                    ranit B
                    Hi Ashish,

                    Try these & check which fits your requirement -
                    -- [1]
                    with xx as(
                      select 'LPO001' po_no,'001' id,'LPORD' po_type,'ABC' company,'XYZ' item from dual union all
                      select 'IPO002' po_no,'002' id,'IPORD' po_type,'ABC' company,'XYZ' item from dual
                    )
                    select replace(po_no,id,'') from xx;
                    
                    -- [2]
                    with xx as(
                      select 'LPO001' po_no,'001' id,'LPORD' po_type,'ABC' company,'XYZ' item from dual union all
                      select 'IPO002' po_no,'002' id,'IPORD' po_type,'ABC' company,'XYZ' item from dual
                    )
                    select replace(po_type,'RD','') from xx;
                    both gives o/p as
                    LPO
                    IPO
                    HTH
                    Ranit B.