CODEWITHSUNDEEP
sqloracle-database
Sujan Shrestha
Sujan Shrestha

Reputation: 1040

Case When Condition in Where Clause. Use filter condition if it matches the case when condition only

Is it possible to use case when condition in where clause to filter select statement. For Eg:

Select * from table_name 
where source ='UHC'
and 
to_char(termdate,'YYYYMM') <= '201603';

But i want second filter condition to work only if policy number is '1'. For Eg:

case when policy_number = '1' then to_char(termdate,'YYYYMM') <= '201603';

if the policy number is not 1 then only 1st where clause should work but if policy number is 1 then both the where clause should work. i hope i made my situation clear.

Upvotes: 0

Views: 92

Answers (1)

Kacper
Kacper

Reputation: 4818

You don't need case at all:

Select * from table_name 
where source ='UHC'
and ((policy_number = '1' and to_char(termdate,'YYYYMM') <= '201603') 
     or nvl(policy_number, '0') != '1');

With case condition will be like:

where source ='UHC' and case when policy_number = '1' then to_char(termdate,'YYYYMM') else '000000' end <= '201603');

in else you need something that is always less than '201603'. Another problem here is why you're comparing numbers as varchars? Is it really what you need?

Upvotes: 2

Related Questions