I have a main query that have a bunch of cross joins in it such as:
SELECT val1, val2, ... FROM ( SELECT top 1 id as val1 FROM TableA ) A CROSS JOIN ( SELECT top 1 id as val2 FROM TableB ) B CROSS JOIN ( ? ) C
Inside the third CROSS JOIN C I want to place a query that contains a CTE in it (this one basically allows me to execute a query only if another query has no results), which looks like the following:
WITH r AS ( SELECT * FROM film WHERE length = 120 ) SELECT CASE WHEN @flag = 'Y' THEN ( SELECT * FROM r UNION ALL SELECT * FROM film WHERE length = 130 AND NOT EXISTS ( SELECT * FROM r ) ) ELSE NULL END val3
Is this possible?