Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert IN to EXISTS.

Akbar JalaluddinOct 22 2021

Dear friends,
i am trying to rewrite a query and it seems nothing is working. I am not sure where I am doing wrong. Can you please correct the query? All I am trying to do is join two tables and use another table using IN condition which i wanted to convert to EXISTS

select DISTINCT
B.RCODE,
B.SCODE,
B.SSD,
A.NH_ID
from STAGE.TABLEA A
join
STAGE.TABLEB B
on
A.KEYID=B.KEYID
where
A.IS_ACTIVE_IND='Y'
AND B.IS_ACTIVE_IND='Y'
AND
A.STATUS_IND='LAST'
AND
B.STATUS_CODE='FINAL'
AND
(B.SCODE in (SELECT CODE_VALUE from
DATAMART.CODES WHERE RTYPE ='SCODE' and U_TYPE = 'ACC')

OR
B.RCODE IN (SELECT CODE_VALUE from
DATAMART.CODES WHERE RTYPE ='RCODE' and U_TYPE = 'ACC'))

--TRYING USING EXISTS
select DISTINCT
B.RCODE,
B.SCODE,
B.SSD,
A.NH_ID

from STAGE.TABLEA A
WHERE EXISTS (SELECT B.RCODE,B.SCODE,B.SSD FROM STAGE.TABLEB B,DATAMART.CODES C
WHERE A.KEYID=B.KEYID
AND
B.SCODE=C.CODE_VALUE AND RTYPE IN ('SCODE','RCODE') AND U_TYPE='ACC'
AND B.IS_ACTIVE_IND='Y'
AND B.STATUS_CODE='FINAL'
)
AND A.IS_ACTIVE_IND='Y'
AND A.STATUS_IND='LAST'
I am not able to execute the EXISTS clause query at all. Any suggestions please?

This post has been answered by Frank Kulash on Oct 22 2021
Jump to Answer

Comments

Post Details

Added on Oct 22 2021
3 comments
1,052 views