sgmoore sgmoore - 1 month ago 9
SQL Question

Using a If select ... else select .. statement in a subquery

Suppose I have a sql query like the following.

DECLARE @Date1 datetime = (SELECT TOP 1 Date FROM Table1 ORDER BY Date)
DECLARE @Date2 datetime = (SELECT Date FROM Table2 WHERE Status = 'xxx')

IF @Date1 >= @Date2
SELECT Column1, Column2 From Table3
ELSE
SELECT Column1, Column2 FROM Table4


Is it possible to use this query inside another query.
I need to use union to combine this results from this query to another query and then perform a grouping etc and further selection, but if I wrap a
select * from (
around this query I get a syntax error.

At the moment I am performing separate queries and then combining and grouping the results in memory objects, which works but isn't ideal and was wondering if there was a better way

Answer

I think you can do this:

SELECT *
FROM (
   SELECT Column1, Column2 
   From Table3
   WHERE @Date1 >= @Date2

UNION ALL

   SELECT Column1, Column2 
   FROM Table4
   WHERE @Date1 < @Date2 ) AS t