Clever Geek Handbook

Can I join a table in ORACLE (10g) using a CASE clause in an ON statement (or even where the clause is an inner join)

I am trying to make the following code smaller. Is it possible?

select a.*
from table1 a
WHERE a."cola1" = 'valuea1'
UNION ALL
select a.*
from tablea1 a
inner join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
  and b."colb3" = 'valueb3'

I am actually looking for records from table1 for value1 or value2, but for records that match value2, I want to apply 1 additional condition which includes joining to the second table. Can this be done without a UNION clause?

The skeleton or what I'm trying to code is below ... but it doesn't work fine.

select a.*
from table1 a
inner join table2 b on a."cola1" = b."COLb1"
WHERE a."cola2" IN ('valuea1','valuea2')
  and 
  CASE 
    WHEN a."cola2" = 'valuea2' THEN b."colb1" = 'valueb3'
    ELSE 1=1
  END CASE
+1


source to share


4 answers


I think the operators CASE

work in connection conditions, but I'm not sure. But will this work for you?

select *
  from table1 a
 where a.cola1 = 'valuea1'
    or (a.cola1 = 'valuea2'
        and Exists(select 1
                     from table2 b
                    where a.cola2 = b.colb2
                      and b.colb3 = 'valueb3'
                   )
        )


Edit: Wouldn't this work?

select a.*
  from table1 a
  Left Outer Join table2 b On (a.cola2 = b.colb2)
 where a.cola1 = 'valuea1'
    or (a.cola1 = 'valuea2' and b.colb3 = 'valueb3')
+5


source


In general, you should follow Hosam's advice to completely rewrite the predicate. But to explain your original problem, the problem is that in SQL CASE .. END is an expressionand can only be used where any other expression can be used. A condition like "a = b" is two expressions linked by a logical operator. You can think of it as a boolean expression, but that's not how SQL treats it.

You can accomplish what you want with CASE by using it as one of two expressions in a condition:



WHERE a."cola2" IN ('valuea1','valuea2')
  and 
  b."colb1" = CASE 
                WHEN a."cola2" = 'valuea2' THEN 'valueb3'
                ELSE b."colb1"
              END CASE

(If NULL can be included for colb1, you will need to modify it to handle that.)

+4


source


You can achieve this using left join and where the condition is

select a.*
from table1 a
left join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
  and ( b."colb2" is null or b."colb3" = 'valueb3' )
0


source


OP: I have a mini workaround that comes close (this can only work if it's an inner join.)

select a. * from table1 a internal table join2b on. "cola1" = b. "COLb1" WHERE (a. "Cola2" = 'valuea1') OR (a. "Cola2" = 'valuea2' and b. "Colb1" = 'valueb3')

Sometimes writing code can trigger alternative thinking. Self-therapy. Thanks for your input.

-1


source






More articles:


All Articles