How create just one Oracle view....

[raf]

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!

[DaPi]

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

[tamilselvan]

10:20:08 SQL> select * 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)
          end) newstr
   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