CODEWITHSUNDEEP
sqloracle-database
amar2108
amar2108

Reputation: 323

Setting Condition in Case Statement

Suppose there is a table structure like this

Name | Class | CS_ex_date | IT_ex_date|

xyz  | CSE   | 10 june    |  Null     |
123  | ECE   | Null       |  Null     |
456  | MECH  | Null       |  Null     |
678  | MECH  | Null       |  Null     |
abc  | IT    | Null       | 3 Aug     |

I want to create a select statement using case statement and within case statements are some conditions.

I have seen the usual syntax for case statements and case statements in where conditions, but i want to put some conditions inside the THEN part. I am having problem with the syntax and cant find any example to take reference from.

My query is something like this:

select * 
  from student 
 where (case 
          when class like '%SE%' 
            then CS_ex_date > sysdate and CS_ex_date < sysdate + 60
          when class like '%T%' 
            then IT_ex_date > sysdate and IT_ex_date < sysdate + 60 
        end);

I am not sure about the syntax of my query and getting ORA-00905: missing keyword.

The expected output should be 

Name | Class | CS_ex_date | IT_ex_date|

xyz  | CSE   | 10 june    |  Null    |
abc  | IT    | Null       | 3 Aug    |

Is there any way around this. That produces the same result using any other method.

Upvotes: 0

Views: 305

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

You may use the CASE statement to calculate the value relevant to the predicate in a subquery and than use it in the WHERE condition:

with student_dt as
(select 
   student.*,
   case 
          when class like '%SE%' 
            then CS_ex_date  
          when class like '%T%' 
            then IT_ex_date  
        end as check_date
  from student)
select * from  student_dt 
 where check_date > sysdate and check_date < sysdate + 60;

This makes the query a bit more verbose, but preserves the intention that could be lost in the transformation of the predicate.

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186678

It seems you want or, not case:

select * 
  from student 
 where (class like '%SE%' and CS_ex_date > sysdate and CS_ex_date < sysdate + 60) or
       (class like '%T%'  and IT_ex_date > sysdate and IT_ex_date < sysdate + 60)

Upvotes: 1

Related Questions