All Forums Database
eejimkos 73 posts Joined 01/12
28 Jul 2012
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.

 

 

 

Qaisar Kiani 337 posts Joined 11/05
28 Jul 2012

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...

eejimkos 73 posts Joined 01/12
29 Jul 2012

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.

You must sign in to leave a comment.