CODEWITHSUNDEEP
sqlsql-servert-sql
pRintLn
pRintLn

Reputation: 149

(Common Table Expressions) CTE as part of WHERE clause... possible?

Is it possible to use CTE in a WHERE clause e.g.

SELECT * 
  FROM Table1 
 WHERE Table1.PK IN (
   WITH Cte AS (
    -- root selection (dynamic, generated in code)
    SELECT Bla FROM Table2
    -- recursive part
    UNION ALL
    SELECT …..)
  SELECT Bla FROM Cte)

The reason I’m asking is that I need to use a recursive query and the only way of doing it at the moment without updating our framework is to place it in the where clause.

Upvotes: 7

Views: 15012

Answers (2)

Naveed Zahid
Naveed Zahid

Reputation: 41

Yes you can apply WHERE CLAUSE with CTE. You have to make Table-Valued Function and return result in Table. then you can use this result set in any query with WHERE Clause. Go to the link and find example:
http://muhammadnaveed.info/use-cte-query-in-where-clause/ Hope this will help you.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332661

No, WITH clauses need to be defined before the main SELECT. Like this:

WITH recursive_cte AS (
   -- root selection (dynamic, generated in code)
   SELECT Bla FROM Table2
   -- recursive part
   UNION ALL
   SELECT …..)
SELECT t.*
  FROM TABLE1 t
  JOIN recursive_cte rc ON rc.key = t.pk

I also tweaked the query to use a JOIN instead, but you'll have to watch for duplicates.

Upvotes: 4

Related Questions