CODEWITHSUNDEEP
sqloracle-database
1pluszara
1pluszara

Reputation: 1528

Oracle: Check for existence and map a new column

Would like to check the existence of an id from table_1 in table_2 and based on that trying to create a new column mapped_value. table_2 has huge number of records with duplicate id's and also has a non-unique index on it.

SQL> drop table table_1
Table dropped.
SQL> create table table_1(id varchar2(10),value varchar2(10))
Table created.
SQL> insert into table_1
(select '100','ABC' from dual
union all
select '101','DEF' from dual
union all
select '103','GHI' from dual
)
3 rows created.
SQL> commit
Commit complete.
SQL> select * from table_1

ID         VALUE     
---------- ----------
100        ABC       
101        DEF       
103        GHI       

3 rows selected.
SQL> drop table table_2
Table dropped.
SQL> create table table_2(id varchar2(10),value varchar2(10),day date)
Table created.
SQL> insert into table_2
(select '100','ABC',sysdate from dual
union all
select '100','ABC',sysdate from dual
union all
select '100','ABC',sysdate from dual
union all
select '101','DEF',sysdate from dual
union all
select '101','DEF',sysdate from dual
union all
select '101','DEF',sysdate from dual
)
6 rows created.
SQL> commit
Commit complete.
SQL> select * from table_2

ID         VALUE      DAY      
---------- ---------- ---------
100        ABC        18-SEP-18
100        ABC        18-SEP-18
100        ABC        18-SEP-18
101        DEF        18-SEP-18
101        DEF        18-SEP-18
101        DEF        18-SEP-18

6 rows selected.

Trying below but its getting duplicate records for ids 100 and 101. I know,shouldn't use outer join as there are duplicates. I want to get the desired output but without duplicates by leveraging the non-unique index on table_2. How do go about this?

SQL> select t1.*,case when t2.id is null then '***EMPTY****' else t2.id end as mapped_value 
from table_1 t1,table_2 t2
where t1.id = t2.id(+)

ID         VALUE      MAPPED_VALUE
---------- ---------- ------------
100        ABC        100         
100        ABC        100         
100        ABC        100         
101        DEF        101         
101        DEF        101         
101        DEF        101         
103        GHI        ***EMPTY****

7 rows selected.

Upvotes: 0

Views: 57

Answers (1)

sticky bit
sticky bit

Reputation: 37487

If I understand that correctly, an EXISTS in a CASE might be what you're after.

SELECT t1.id,
       t1.value,
       CASE
         WHEN EXISTS (SELECT *
                             FROM table_2 t2
                             WHERE t2.id = t1.id) THEN
           t1.id
         ELSE
           '***EMPTY***'
       END mapped_value
       FROM table_1 t1;

Upvotes: 1

Related Questions