Convert IN to EXISTS.

User_DIL60

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?

Frank Kulash

Hi,

If you want to replace the IN sub-queries with EXISTS, and leave all the other conditions alone, then this may be one way

 AND   EXISTS (
		  SELECT  0
		  FROM    department_codes
		  WHERE   rtype       = 'SCODE'
		  AND	  u_type      = 'ACC'
		  AND	  code_value  IN (b.scode, b.rcode)
	     )

Of course, I can't test it without any sample dat.

Frank Kulash

Hi, @User_DIL60

Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

There are lots of differences besides changing IN to EXISTS in those two queries. To give just one example: In the first query you posted, the conditions About 'FINAL' is independent of the IN. If you just want to use EXISTS instead of IN, then that condition should be independent of the EXISTS, but it's not. This would be easier to see if you formatted you code.

The WHERE clause of your first query is:

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'
			)
	)

The WHERE clause of the second query is:

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'

Since the EXISTS sub-query doesn't pass back any data, most people always use EXISTS (SELECT NULL FROM ...) or EXISTS (SELECT 0 FROM ...). Putting actual columns in the SELECT clause doesn't change the results, but it might confuse people trying to read the code.

Speaking of that: never use the same table alias more than once on the same statement; it's extremely confusing.

Frank Kulash

Hi,

If you want to replace the IN sub-queries with EXISTS, and leave all the other conditions alone, then this may be one way

 AND   EXISTS (
		  SELECT  0
		  FROM    department_codes
		  WHERE   rtype       = 'SCODE'
		  AND	  u_type      = 'ACC'
		  AND	  code_value  IN (b.scode, b.rcode)
	     )

Of course, I can't test it without any sample dat.

User_DIL60

Sorry about that. I think i was able to crack it with the example specified above. Thanks again.