select statement/subquery in CASE while condition check in oracle SQL

Question

select sum(COLUMN1) from TABLE1 
where TRUNC(INPUT_DATE) >= CASE WHEN TRUNC(SYSDATE) <= TRUNC(TO_DATE('02-MAR-22')) THEN 
DATE1 ELSE DATE2 END;

Above SQL query working fine, can anyone help me is this possible can i use a subquery 'select date from table2' instead of hard code value TRUNC(TO_DATE('02-MAR-22')).

Answer 1

Assuming there is only a single row in table2 then you can use:

select sum(COLUMN1)
from   TABLE1 
where  TRUNC(INPUT_DATE) >= CASE
                            WHEN TRUNC(SYSDATE) <= (SELECT date_column
                                                    FROM   table2)
                            THEN DATE1
                            ELSE DATE2
                            END;

If there are multiple rows then you will need to correlate on something so that only a single row is returned by the sub-query:

select sum(COLUMN1)
from   TABLE1 t1
where  TRUNC(INPUT_DATE) >= CASE
                            WHEN TRUNC(SYSDATE) <= (SELECT date_column
                                                    FROM   table2 t2
                                                    WHERE  t1.something = t2.something)
                            THEN DATE1
                            ELSE DATE2
                            END;

or filter so only a single row is returned:

select sum(COLUMN1)
from   TABLE1
where  TRUNC(INPUT_DATE) >= CASE
                            WHEN TRUNC(SYSDATE) <= (SELECT date_column
                                                    FROM   table2
                                                    ORDER BY something
                                                    FETCH FIRST ROW ONLY)
                            THEN DATE1
                            ELSE DATE2
                            END;

Else you could aggregate in the sub-query (which will always return a single row):

select sum(COLUMN1)
from   TABLE1 t1
where  TRUNC(INPUT_DATE) >= CASE
                            WHEN TRUNC(SYSDATE) <= (SELECT MIN(date_column)
                                                    FROM   table2)
                            THEN DATE1
                            ELSE DATE2
                            END;

or, combining two previous options:

select sum(COLUMN1)
from   TABLE1 t1
where  TRUNC(INPUT_DATE) >= CASE
                            WHEN TRUNC(SYSDATE) <= (SELECT MIN(date_column)
                                                    FROM   table2 t2
                                                    WHERE  t1.something = t2.something)
                            THEN DATE1
                            ELSE DATE2
                            END;

Etc.

YOU need to work out what your business logic should be and then apply the most appropriate SQL logic in that case.