Search code examples
sqlsubquerycasecommon-table-expression

Call a CTE based on a criterion - Case statement SQL

I have two CTEs with the same structure but reading data from two different tables.

Based on a date filter how do I call a CTE using an IF or CASE statement in SQL?

Something like this:

with combined_query as
(
 select * from CTE1 
 union all
 select * from CTE2
)
select 
case when date >= '2023-01-01' select * from CTE1
else select * from CTE2 end as criteria
from combined_query

The date column is in both CTEs.

Solution

  • Maybe try using WHERE like this?

    with combined_query AS (
    SELECT *, 'CTE1' AS source FROM CTE1
    UNION ALL
    SELECT *, 'CTE2' AS source FROM CTE2
)
SELECT *
FROM combined_query
WHERE (date >= '2023-01-01' AND source = 'CTE1')
   OR (date < '2023-01-01' AND source = 'CTE2');