CODEWITHSUNDEEP
sqloracle-database
Nidheesh
Nidheesh

Reputation: 812

CASE inside where clause with null checking

I want to return the rows from a table which has null and not null conditions. It works when I equate the column closdt with a static values, but do not works with null values checking condition inside case statement. closdt is column in table sbm

select * from sbm where
closdt  
case when :chk='Y' then 
  is null 
else 
  is not null 
end;

Upvotes: 0

Views: 154

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

There's no way to break up a conditional test using a CASE expression as you've shown. You can use a CASE expression to generate a value to test against, as shown below:

select *
  from sbm
  where case
          when :chk = 'Y' AND closdt IS NULL THEN 1
          when :chk <> 'Y' AND closdt IS NOT NULL THEN 1
          else 0
        end = 1

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

You can write your condition with some boolean logics:

:chk='Y' and closdt is null 
OR
:chk is null
OR
:chk != 'Y'

Upvotes: 3

Related Questions