Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use CASE to format the code in a single view:

    Code:
    create or replace view v_cod (cod_id)
    as
    select 
      case
      when <condition>
      then <expression1>
      else <expression2>
      end cod_id
    from tab_id

Posting Permissions

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