Search code examples
sqlcase

How to write select queries inside case condition?

How to write select queries inside case condition? I am writing a SQL statement with case statement where I can run the select query inside case.

Below query will return the entry of sysdate-1 if current time is before 3.00 AM, else return the entry of sysdate-2.

select case when to_number(to_char(trunc(sysdate, 'hh'), 'h24')) in (00, 01, 02) then
           select * from table where datetime > sysdate-1
       else
           select * from table where datetime > sysdate-2
       end

I am getting a syntax error. Can anyone please help me?

Solution

  • Use regular AND/OR instead:

    select *
from table
where (to_number(to_char(trunc(sysdate, 'hh'), 'h24')) not in (00, 01, 02)
       and datetime > sysdate - 2)
   or datetime > sysdate - 1       -- i.e. "in (00, 01, 02)"

    (Most products find AND/OR easier to optimize than case expressions.)