How to write select queries inside case condition?

Question

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?

https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Jun 09 '22 at 11:13
This is not how SQL works. You need to move the case into the where condition of your select statement. You cannot produce multiple rows (and in some (most?) dialects, not even multiple columns) in a case statement. Also, as I guess this is Oracle, you cannot have a select without a `from` clause. — Mark Rotteveel, Jun 09 '22 at 11:14
Is there any way in SQL, so that my requirement can be achieved ? — Beginner, Jun 09 '22 at 11:17
You could attempt this using dynamic sql but it'll be a nightmare. https://stackoverflow.com/questions/22216805/executing-dynamic-sql-query-in-oracle — JonTout, Jun 09 '22 at 11:43

score 1 · Answer 1 · answered Jun 09 '22 at 12:08

1

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

answered Jun 09 '22 at 12:08

Ross Bush

14,648
2
32
55

score 1 · Accepted Answer · answered Jun 09 '22 at 12:08

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

How to write select queries inside case condition?

2 Answers2