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

User_EI49E

I found the problem. I added connector port 8444 to Tomcat server.xml and all is well.

1 - 1

Post Details

Added on Oct 22 2021
3 comments
1,058 views