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?