Sql-server – put a CTE inside a cross join

ctesql server

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?

Best Answer

Some SQL products – like PostgreSQL, for instance – allow you to have nested CTE definitions, enabling you to write your queries like this:

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 
 (
    WITH r AS (                              -- nested CTE definition
      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
  ) C

SQL Server does not support this, though, as ypercubeᵀᴹ explains in a comment.

However, as per another comment by ypercubeᵀᴹ, you can move the CTE definition to the beginning of the entire query. That way you you can still reference it in the C derived table. So your query will look like this:

WITH r AS (                              -- the CTE definition goes here
  SELECT * FROM film WHERE length = 120
)
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 
 (
    SELECT CASE WHEN @flag = 'Y' THEN (
        SELECT * FROM r                  -- this CTE reference is valid
        UNION ALL
        SELECT * FROM film
        WHERE length = 130
        AND NOT EXISTS (
          SELECT * FROM r                -- as is this one
        )
    ) ELSE NULL END val3
  ) C
Related Question