CODEWITHSUNDEEP
oracle-databaseexists
RedFux227
RedFux227

Reputation: 101

Change IN to EXISTS

I have this 2 query :

Query #1

SELECT A1.clrn_id  ,A1.gpgroup ,A1.cl_id
FROM p_dtl A1
WHERE A1.PYMT_DT = TO_DATE(:1 ,'YYYY-MM-DD') 
and A1.clrn_id in (
                     select clrn_id  
                     from gp_cl_rn run  
                     where run.clrn_id = a1.clrn_id 
                     and run.finalized_ind = :2
                  )
                    AND cl_id IN 
                        ( 
                            SELECT cl_id 
                            FROM hm_sal 
                            WHERE oprt_id ='004038'  
                            AND runctrl_id = :3
                        )

Query #2

SELECT B.eid
FROM JBB B
WHERE B.eid IN 
(
    (
        SELECT eid
        FROM pbank A
        WHERE (bankid,branchid) IN 
        (
            SELECT bankid  ,branchid  
            FROM pbranch 
            WHERE sourceid = :1
            AND estat=:2
        )
        AND ESTAT = :2
        AND acct_type = :3
        AND acct_id = (
                select max(D.acct_id) 
                from pbank D 
                where D.eid = A.eid
                AND D.ESTAT = :2
                AND D.acct_type = :3)
     )
)

How can I change the clrn_id for the 1st query and B.eid for the 2nd query into EXISTS function? And are the bankid, branchid and acct_id also changeable into EXISTS function?

Thanks in advance!

Upvotes: 2

Views: 398

Answers (3)

Ben
Ben

Reputation: 52843

You can change this to a where exists, but you probably want to join instead:

SELECT B.eid
  FROM JBB B
  JOIN ( SELECT eid, estat, acct_type, acct_id
              , max(acct_id) over ( partition by eid ) as max_acct
           FROM pbank 
                ) A
    ON b.eid = a.eid
  JOIN pbranch C
    ON a.bankid = c.bankid
   AND a.branchid = c.brahchid
 WHERE c.sourceid = :1
   AND c.estat = :2
   AND a.ESTAT = :3
   AND a.acct_type = :4
   AND a.acct_id = a.max_acct

By using the analytic function max() you remove the need for a sub-select; it's also a lot clearer, I think, to do things in this manner.

And, your newly added query becomes:

SELECT A1.clrn_id, A1.gpgroup, A1.cl_id
  FROM p_dtl A1
  JOIN gp_cl_rn run
    ON A1.clrn_id = run.clrn_id
 WHERE A1.PYMT_DT = TO_DATE(:1 ,'YYYY-MM-DD') 
   AND run.clrn_id = a1.clrn_id 
   AND run.finalized_ind = :2

I notice that you're explicitly using the to_date() function, which implies that you're storing a date as a string. This is bad practice and likely to cause you trouble in the longer run... avoid it if at all possible.

max(acct_id) over ( partition by eid )

is an analytic function; this does exactly the same as the aggregate function max(), except instead of requiring a GROUP BY, it returns the same result for every row in the result set.

This particular use returns the maximum acct_id for every eid. There's a whole host of analytic functions, but the best thing to do is to try it for yourself. There are also several examples available online.

Using a JOIN is not necessarily quicker than a where exists, it just depends what you're after. As with everything I would recommend trying both and seeing what suits your particular situation more.

Generally, they have different purposes; where exists is designed to stop "calculating" rows when a single row that fulfils the conditions is found. JOIN, does everything. In your case as you want everything there may be little to chose between them but I would always use JOIN; just ensure that your indexes are correct.

Upvotes: 3

Robert
Robert

Reputation: 25753

I hope, it will help you:

SELECT B.eid
FROM JBB B
where exists
(
    SELECT 1
    FROM pbank A
    WHERE exists
        (
            SELECT 1 
            FROM pbranch PB
            WHERE PB.sourceid = :1
            AND PB.estat=:2
            and PB.bankid  = A.bankid
            AND PB.branchid  = A.branchid
        )
        AND ESTAT = :2
        AND acct_type = :3
        AND A.eid = B.eid
        AND acct_id = (
                select max(D.acct_id) 
                from pbank D 
                where D.eid = A.eid
                AND D.eid = B.eid
                AND D.ESTAT = :2
                AND D.acct_type = :3)
)

Upvotes: 2

armen.shimoon
armen.shimoon

Reputation: 6401

SELECT B.eid
FROM JBB B
JOIN
(
    (
        SELECT eid
        FROM pbank A
        WHERE (bankid,branchid) IN 
        (
            SELECT bankid  ,branchid  
            FROM pbranch 
            WHERE sourceid = 'BNIATB'
            AND estat='A'
        )
        AND ESTAT = 'A'
        AND acct_type = 'S'
        AND acct_id = (
                select max(D.acct_id) 
                from pbank D 
                where D.eid = A.eid
                AND D.ESTAT = 'A'
                AND D.acct_type = 'S')
     )
) C on B.eid = c.EID

Upvotes: 1

Related Questions