CODEWITHSUNDEEP
plsqloracle11g
Casey
Casey

Reputation: 223

CASE construct in where clause

I need a filter condition to be included in a select query when a condition wrapped in a function is satisfied. If condition not satisfied, then filter condition should not be applied.

Code: 

select col_a,col_b,cancel_Date from tab_a 
where col_c = <<some_condition>>  
and (case when oracle_function() = 'YES' then 'tab_a.cancel_date is null'
      else null
    )

If I write it this way, then it throws off "invalid relational operator" error. Realising that case should resolve to a value on the right,i tried adding something like this which does not give me the expected result when the condition is satisfied:

select col_a,col_b,cancel_Date from tab_a 
where col_c = <<some_condition>>  
and (case when oracle_function() = 'YES' then 'tab_a.cancel_date is null'
       else '1'
      ) = '1'

My expected output should be:

If oracle_function() = YES, then my query should effectively resolve to:

select col_a,col_b,cancel_Date from tab_a 
where col_c = <<some_condition>> and tab_a.cancel_date is null;

Else:

select col_a,col_b,cancel_Date from tab_a 
where col_c = <<some_condition>>;

Any thoughts ?

Upvotes: 1

Views: 475

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Instead of the case statement, this query requires that either the function returns a value other than 'YES', or that the date is null... which should be analogous to your original query:

select col_a,col_b,cancel_Date 
from tab_a 
where col_c = <<some_condition>>  
and (oracle_function() <> 'YES' OR tab_a.cancel_date is null)

Upvotes: 2

Related Questions