CASE error in ORACLE’s WHERE

Asked

Viewed 6,990 times

2

I am trying to use the case function in the Where clause in Oracle but this part of the query generates an error. It is possible to use this function to determine which Join will be made?

AND (CASE 
WHEN (t1.cd1 IS NOT NULL) THEN (t2.cd1 = t1.cd1 )
ELSE (t3.cd2 = t1.cd2)
END)
oracle

2 answers

2


You don’t need CASE for this, just use common boolean logic.

AND(
(t1.cd1 IS NOT NULL AND t2.cd1 = t1.cd1)
OR
(t3.cd2 = t1.cd2)
)

BTW, yes! you can do something like:

where column_1 = <Some_value>
and (case when p_call_location = 'A' and column_2 like '%ABC%' then 'VALID'
when p_call_location = 'B' and column_2 Not Like '%ABC%' then 'VALID'
when p_call_location = 'C' then column_3 like '%EFG%'
else 'INVALID'
END) = 'VALID';

But I suspect that using pure logic (no case) is (possibly) better

Note: When an error occurs (probably in your case a syntax error) put the error together because it makes it easier to give an answer

0

It would be something like that:

AND CASE WHEN (t1.cd1 IS NOT NULL) 
         THEN t2.cd1 
         ELSE t3.cd2
    END = t1.cd2

In the first condition if t1.cd1 is not null, it brings the column t2.cd1, if t1.cd1 is null, it brings the column T3.cd2. Then it compares the value with the t1.cd2 column

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.