Can I join to a table in ORACLE (10g) using a CASE clause in the ON statement (or even where clause as it's an inner join)?

I think CASE statements work in join conditions, but I'm not sure. But would this work for you? Select * from table1 a where a.

Cola1 = 'valuea1' or (a. Cola1 = 'valuea2' and Exists(select 1 from table2 be where a. Cola2 = b.

Colb2 and b. Colb3 = 'valueb3' ) ) Edit: Wouldn't this simply work? Select a.

* from table1 a Left Outer Join table2 be On (a. Cola2 = b. Colb2) where a.

Cola1 = 'valuea1' or (a. Cola1 = 'valuea2' and b. Colb3 = 'valueb3').

Overall you should follow Hosam's suggestion of rewriting the predicate entirely. But to explain your original problem further, the issue is that in SQL, CASE .. END is an expression and can only be used where any other expression could be used. A condition like "a=b" is two expressions connected by a logical operator.

You may want to think of it as a boolean expression but that's not the way SQL views it. You could accomplish what you want with CASE by using it as one of the two expressions in the condition, like so: WHERE a. "cola2" IN ('valuea1','valuea2') and b."colb1" = CASE WHEN a.

"cola2" = 'valuea2' THEN 'valueb3' ELSE b. "colb1" END CASE (If it is possible for colb1 to include NULLs you would need to modify to handle that.).

OP: Again Hosam: Part 2 is Same angle I though of myself....thanks. Part 1 may solve the optional aka left join problems. I hadn't thought of the subquery angle.

No, part 2 is inherently different from yours. Notice the LEFT OUTER JOIN part. My query would produce results when the join fails, but yours will not produce them in such a case.

– Hosam Aly Jan 9 '09 at 11:50 And by the way, you could use comments instead of writing answers. :) – Hosam Aly Jan 9 '09 at 11:56.

OP: I've got a mini-workaround which goes close (This may only work given this is an inner join. ) select a. * from table1 a inner join table2 be on a."cola1" = b.

"COLb1" WHERE (a. "cola2" = 'valuea1') OR (a."cola2" = 'valuea2' and b. "colb1" = 'valueb3') Sometimes writing code out can prompt some alternative thinking.

Self-Therapy sort of. Thanks for your input.

This doesn't achieve the same objective. This query won't produce values where a. Cola2 = 'valuea1' unless the inner join succeeds.

Check my solution for some alternatives. – Hosam Aly Jan 9 '09 at 11:48.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.