Forum Stats

  • 3,769,814 Users
  • 2,253,026 Discussions
  • 7,875,214 Comments

Discussions

Convert IN to EXISTS.

User_DIL60
User_DIL60 Member Posts: 20 Green Ribbon

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?

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    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.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    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
    User_DIL60 Member Posts: 20 Green Ribbon

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