case statement on join condition.

Database
Enthusiast

case statement on join condition.

Hello,

Thanks in advance for your time and responses.

I would like to ask you a question about having a case when statement on a join condition.

SELECT tableB.theColumnINeed

FROM   tableA

LEFT OUTER JOIN tableB

ON  tableA.myColumn =

   CASE

    WHEN tableA.myDateColumn = something THEN xx

    ELSE something_else

   END

to -->

SELECT tableB.theColumnINeed

FROM   tableA

    LEFT OUTER JOIN tableB

         ON tableA.myDateColumn = something

        AND tableA.myColumn =  condition_needed

UNION ALL

SELECT tableB.theColumnINeed

FROM   tableA

    LEFT OUTER JOIN tableB

         ON tableA.myDateColumn = something_else

        AND myColumn = condition_needed_1

Is this a good solution ?  Execution plan i can provided it from Monday.

LEt's say that the two table are the results from two other queries , about 5 M rows ,  I do not remember now how the spool was restributed.  I will try to make volatile from these two , tableB and tableA -with the same PI --

Any ideas? recomendation?

Thank you very much.

2 REPLIES
Enthusiast

Re: case statement on join condition.

Yes, you can have a CASE statement in JOINing condition because all the case statements return scalar values, so shouldn't be a problem. I think your first query should execute without any error.

If you want to optimize the query then thats a different story. You have to provide more details like the table structure (PI/SI etc), explain plan, complete query etc...

Enthusiast

Re: case statement on join condition.

Hi,

thanks for your quick reply .

I want to optimize the query because they gave it to me with the case statement on the join condition. I will do some tests on Monday and i will try to post ,PI / partitions / explain plan here.

Furthermore , it is not simple as i wrote before , because the two tables are in reality , the result from other other joins.

Thank once more.