-
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!
-
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
-
PHP Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|