How to write select queries inside case condition?-CodePudding

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?

CodePudding user response：

You have the logic sort of inverted. Move the case to your where clause and it will work as expected.

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

CodePudding user response：

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.)