This discussion is archived
6 Replies Latest reply: Nov 17, 2012 4:28 AM by ranit B RSS

Automatic Item code

only.ashish99 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    PO_type || ID
    SY.
  • 2. Re: Automatic Item code
    only.ashish99 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    only.ashish99 wrote:

    how to trim this RD?
    SUBSTR(PO_type,1,3) || ID
    SY.
  • 5. Re: Automatic Item code
    Hoek Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points