I am trying to use the case function in the where clause in Oracle but this part of the query generates an error. Is it possible to use this function to determine which join is going to be done?
AND (CASE
WHEN (t1.cd1 IS NOT NULL) THEN (t2.cd1 = t1.cd1 )
ELSE (t3.cd2 = t1.cd2)
END)
You do not need CASE for this, just use ordinary 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 this:
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 (without a case) is (possibly) better
Note: When you give an error (probably in your case a syntax error) it puts the error together because it makes it easier to give an answer
It would look something like this:
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 returns the t2.cd1 column, if t1.cd1 is null, it returns the t3.cd2 column. Then it compares the value with the t1.cd2 column