CODEWITHSUNDEEP
oracle-databasecasewhere-clauseclause
user5122076
user5122076

Reputation:

Checking case in where condition oracle

This is the query i have used to check condition. So far i didn't get the solution. When am execute got error as Missing keyword. How to do this.

 select * from customer where status='A' and (case when trim(member_since) is not null then member_since > TO_DATE ( '20160112 235959' , 'YYYYMMDD HH24MISS')
 else registration_date <=TO_DATE ( '20160112 235959' , 'YYYYMMDD HH24MISS') end)

Upvotes: 0

Views: 29

Answers (2)

Jaseer
Jaseer

Reputation: 52

select *
from customer
where status='A'
  and trim(member_since) is not null and  member_since > TO_DATE ('20160112 235959', 'YYYYMMDD HH24MISS') 

  union all

  select *
from customer
where status='A'
  and trim(member_since) is  null and    registration_date <=TO_DATE ('20160112 235959', 'YYYYMMDD HH24MISS')

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You can rewrite with a bit of boolean logic:

SELECT *
FROM customer
WHERE     status = 'A'
   AND ( TRIM(member_since) IS NOT NULL AND member_since > TO_DATE('20160112 235959', 'YYYYMMDD HH24MISS') OR
         TRIM(member_since) IS NULL AND registration_date <= TO_DATE('20160112 235959', 'YYYYMMDD HH24MISS')
       )

Upvotes: 1

Related Questions