CODEWITHSUNDEEP
sqloracle-database
Arnab
Arnab

Reputation: 195

Converting inline query using join

I am trying to replace the inline query present and replace the same using join. However the results obtained are differing from the actual results.

SELECT CLM.ID,
       EL.ERAC_ACTP_CD,
       CLM.ELCT_ID,
       CLM.CLMBT_ID,
       DECODE(CLM.PROVIDER,
              NULL,
              (SELECT NVL(NAME, '')
                 FROM PROVIDER
                WHERE ID = PRVD_ID
                  AND EE_ID = CLM.EE_ID),
              CLM.PROVIDER) PROVIDER_NAME,
       CB.CLAIM_TYPE,
       EL.ACCOUNT_NO
  FROM ELECTION EL, CLAIM CLM, CLAIM_BATCH CB
 WHERE EL.ID = CLM.ELCT_ID
   AND CLM.ID = 1
   AND EL.EE_ID = 1
   AND EL.ID = CLM.ELCT_ID
   AND CB.ID = CLM.CLMBT_ID;

NOTE - CLM.PRVD_ID column value can be null.

Initially I had tried using the below query using inner join. This is not working as PRVD_ID can be null. Hence I don't receive any output whereas I received a row in the original query.

SELECT CLM.ID,
           EL.ERAC_ACTP_CD,
           CLM.ELCT_ID,
           CLM.CLMBT_ID,
DECODE(CLM.PROVIDER,
NULL,
PR.NAME,
CLM.PROVIDER) PROVIDER_NAME,
CB.CLAIM_TYPE,
EL.ACCOUNT_NO
FROM ELECTION EL, CLAIM CLM, CLAIM_BATCH CB, PROVIDER PR
WHERE EL.ID = CLM.ELCT_ID
AND CLM.ID = 1
AND EL.EE_ID = 1
AND EL.ID = CLM.ELCT_ID
AND CB.ID = CLM.CLMBT_ID  
AND  PR.ID = CLM.PRVD_ID

Could you please suggest how do I achieve this?

Upvotes: 0

Views: 684

Answers (2)

Boneist
Boneist

Reputation: 23588

To turn the scalar subquery into a join, you need to use an outer join rather than an inner join, using the correlation predicates as the join condition.

In your case, that would mean the query would look something like:

SELECT clm.id,
     el.erac_actp_cd,
     clm.elct_id,
     clm.clmbt_id,
     COALESCE(clm.provider, pr.name) provider_name,
     cb.claim_type,
     el.account_no
FROM   election el
       INNER JOIN claim clm ON el.id = clm.elct_id
       INNER JOIN claim_batch cb ON clm.clmbt_id = cb.id
       LEFT OUTER JOIN provider pr ON clm.ee_id = pr.ee_id AND pr.id = clm.prvd_id
WHERE  clm.id = 1
AND    el.ee_id = 1;

N.B. I've turned your DECODE into a COALESCE since you wanted to display clm.provider if that was not null or the pr.name if it was null. COALESCE simply returns the first non-null value.

I've also converted your query to use ANSI joins, rather than the old-style joins; this makes it much clearer to understand what is a join condition and what is a predicate.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191315

Initially I had tried using the below query using inner join. This is not working as PRVD_ID can be null.

You need to use an outer join, which is much simpler to follow with ANSI syntax (though you should be using that anyway); something like:

SELECT CLM.ID,
  EL.ERAC_ACTP_CD,
  CLM.ELCT_ID,
  CLM.CLMBT_ID,
  DECODE(CLM.PROVIDER, NULL, PR.NAME, CLM.PROVIDER) PROVIDER_NAME,
  CB.CLAIM_TYPE,
  EL.ACCOUNT_NO
FROM ELECTION EL
JOIN CLAIM CLM ON CLM.ELCT_ID = EL.ID
JOIN CLAIM_BATCH CB ON CB.ID = CLM.CLMBT_ID
LEFT JOIN PROVIDER PR on PR.ID = CLM.PRVD_ID
WHERE CLM.ID = 1
AND EL.EE_ID = 1;

The

DECODE(CLM.PROVIDER, NULL, PR.NAME, CLM.PROVIDER)

could also be done with a case expression or with nvl() or coalesce():

COALESCE(CLM.PROVIDER, PR.NAME)

And as @Bonest noted, you may need to include an additional condition which was present in your subquery but not in your join attempt:

LEFT JOIN PROVIDER PR on PR.ID = CLM.PRVD_ID
AND PR.EE_ID = CLM.EE_ID

but it isn't clear if you left that out on purpose.

Upvotes: 2

Related Questions