1

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?

  • A CTE can be 'converted' into a subquery – McNets Oct 10 '19 at 17:38
3

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.