DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How create just one Oracle view....

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    How create just one Oracle view....

    Hi,
    I've table TAB_ID

    COD_ID
    G0DE-0-10047514
    M0DF-1-10000281
    T0DH-3-20500819
    R0DJ-2-10603746
    G0DY-0-20006783
    G0DZ-0-20049734
    G0E0-0-10004325
    G0E1-0-10049593
    A20T-0-EMS00038
    D20T-0-EMS00039
    S20T-0-EMS00041
    M20T-0-EMS00234

    I'd like to creare just one view with this output:

    COD_ID
    10-47514
    10-281
    20-500819
    10-603746
    20-6783
    20-49734
    10-4325
    10-49593
    EMS00038
    EMS00039
    EMS00041
    EMS00234

    If SUBSTR(COD_ID,8) is number THEN substr(COD_ID,1,2)||'-'||
    REPLACE(LTRIM(REPLACE(SUBSTR(COD_ID,3),'0',' ')),' ','0')

    If SUBSTR(COD_ID,8) is alphanumeric THEN SUBSTR(COD_ID,8)

    If I create 2 oracle view seems that I get correct output:

    create or replace view v_cod (cod_id)
    as
    select substr(cod_id,8) cod_id
    from tab_id


    create or replace view v_cod_id (cod_id)
    as
    select substr(COD_ID,1,2)||'-'||
    REPLACE(LTRIM(REPLACE(SUBSTR(COD_ID,3),'0',' ')),' ','0') cod_id
    from v_cod
    where cod_id > '00000000'
    AND cod_id < '99999999'
    union all
    select substr(COD_ID,1,2)||'-'||
    REPLACE(LTRIM(REPLACE(SUBSTR(COD_ID,3),'0',' ')),' ','0') cod_id
    from v_cod
    where COD_ID not between '00000000' and '99999999'


    But I must create Just one view


    Can I create only one Oracle View (from TAB_ID) to get this output?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If those two views hold the answer, then I haven't a clue what the question is.

    You might be looking for CASE http://download-west.oracle.com/docs...5a.htm#1033394

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
    10:20:08 SQLselect from my_table ;

    CODE_ID
    ------------------------------
    G0DE-0-10047514
    M0DF
    -1-10000281
    T0DH
    -3-20500819
    R0DJ
    -2-10603746
    G0DY
    -0-20006783
    G0DZ
    -0-20049734
    G0E0
    -0-10004325
    G0E1
    -0-10049593
    A20T
    -0-EMS00038
    D20T
    -0-EMS00039
    S20T
    -0-EMS00041
    M20T
    -0-EMS00234

    12 rows selected
    .

    SQL>  select code_id,
            (case
             
    when ascii(substr(code_id,8,1))>= 48 and 
                  
    ascii(substr(code_id,8,1)) <= 57  then
                 
    (substr(code_id,8,2)||'-'||to_char(to_number(substr(code_id,-6))))
             else 
    substr(code_id,-8)
              
    endnewstr
       from my_table
    ;

    CODE_ID                        NEWSTR
    ------------------------------ -------------------------------------------
    G0DE-0-10047514                10-47514
    M0DF
    -1-10000281                10-281
    T0DH
    -3-20500819                20-500819
    R0DJ
    -2-10603746                10-603746
    G0DY
    -0-20006783                20-6783
    G0DZ
    -0-20049734                20-49734
    G0E0
    -0-10004325                10-4325
    G0E1
    -0-10049593                10-49593
    A20T
    -0-EMS00038                EMS00038
    D20T
    -0-EMS00039                EMS00039
    S20T
    -0-EMS00041                EMS00041
    M20T
    -0-EMS00234                EMS00234

    12 rows selected
    .

    10:20:35 SQL
    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width