Using a case statement in join clause

[pms18066]

I am trying to use a case statement in my join. I have two tables A and B which need to be joined to either table C or table D depending on the type column in table A. Can this be done by joining using a case statement? I haven't been able to get past a missing keyword error. The code is as follows.

From WTM_STG_POLICY P
LEFT JOIN WTM_STG_CLIENT C on substr(P.POL_IDX,1,7) = C.Rec
LEFT JOIN
CASE
WHEN WTM_STG_POLICY.TYPE = 'AUTO' THEN
WTM_STG_AP2DBF D2 on substr(P.POL_IDX,1,7) = substr(D2.REC,1,7)
else
WTM_STG_AP3DBF D3 on substr(P.POL_IDX,1,7) = substr(D3.REC,1,7)
end

 

[SantaMufasa]

PMS,

I believe we can help. Rather than focussing on how to get your syntax to work, could you please offer a functional narrative (business narrative) of what you want in your result set from your four tables and the conditional (CASE) circumstances that specify the appropriate joins?

So what I am looking for is a non-syntactical, conversational explanation of what you want. At that point, I believe we can resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[pms18066]

I have to gather customer information which is in two seperate information tables. if they have an auto policy it will be in table A. If they have another type of policy than it will be in table B. The tables both have other non relevant information in them as well.

The key i policy type which is in another column in table C.

I can use a union and two seperate selects joining on eac table seperately using where type = ... but I wanted to know if a case statement can be used to determine which table I need to join to table C to form the complete row/record.

This would be in the join itself.(me thinkst)

Thanks for the help!!!

 

[LKBrwnDBA]

One solution is to use UNION:

    SELECT ...
      From WTM_STG_POLICY P
    LEFT JOIN WTM_STG_CLIENT C on substr(P.POL_IDX,1,7) = C.Rec
    LEFT JOIN WTM_STG_AP2DBF D2
      ON WTM_STG_POLICY.TYPE =  'AUTO' 
     AND sub substr(P.POL_IDX,1,7) = substr(D2.REC,1,7)
    UNION
    SELECT ...
      From WTM_STG_POLICY P
    LEFT JOIN WTM_STG_CLIENT C on substr(P.POL_IDX,1,7) = C.Rec
    LEFT JOIN WTM_STG_AP3DBF D3
      ON WTM_STG_POLICY.TYPE !=  'AUTO' 
     AND substr(P.POL_IDX,1,7) = substr(D3.REC,1,7)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

PMS,

LK's solution looks very clean and tight to me. I cannot imagine that the Oracle engine is presently able to accommodate the syntax you asserted in your original post. Although I could be wrong about this, I would be very pleasantly surprised if I am wrong.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[pms18066]

Thanks. I was just wondering and thinking it might be faster if it would work without having two seperate selects.