I have query A that uses 2 common tables, and I would like to put query A into query B as a common table along with the other common tables already in query B. So basically I would be creating another common table in query B that consists of query A in its entirety. Can this be done and if so, what's the correct syntax?
Query A (keep in mind i just use generic coding to simpifly the idea)
Code Snippet
WITH ct1 AS
(
SELECT Field1, Field2, Field3
FROM TableA
WHERE Field1 > 5
),
ct2 AS
(
SELECT Field1, Field4, Field5
FROM TableB
WHERE Field4 = ‘1/1/2007’
)
SELECT ct1.Field1, ct1Field2, ct2.Field4
FROM ct1 INNER JOIN ct2 ON ct1.Field1 = ct2.Field1
WHERE ct.2 Field5 = 9
How do I put this query inside another query as a common table since there’s already existing common tables?
I’ve tried this, basically just enclosing it inside the common table syntax, but it just errors out.
Code Snippet
WITH querybce AS
(
WITH ct1 AS
(
SELECT Field1, Field2, Field3
FROM TableA
WHERE Field1 > 5
),
ct2 AS
(
SELECT Field1, Field4, Field5
FROM TableB
WHERE Field4 = ‘1/1/2007'
)
SELECT ct1.Field1, ct1Field2, ct2.Field4
FROM ct1 INNER JOIN ct2 ON ct1.Field1 = ct2.Field1
WHERE ct.2 Field5 = 9
)
I've attempted the idean of joining the two queries into one, but it won't work that way. Any ideas would be greatly appreciated in achieving it this way, if it's even possible! Thanks!
No, you can not have nested CTEs.
Look for "WITH common_table_expression (Transact-SQL)" in BOL.
AMB
|||I don't know what you are trying to do exactly. But you could do:
Code Snippet
WITH ct1 AS
(
SELECT Field1, Field2, Field3
FROM TableA
WHERE Field1 > 5
),
ct2 AS
(
SELECT Field1, Field4, Field5
FROM TableB
WHERE Field4 = '1/1/2007'
)
,
querybce AS
(
SELECT ct1.Field1, ct1Field2, ct2.Field4
FROM ct1 INNER JOIN ct2 ON ct1.Field1 = ct2.Field1
WHERE ct.2 Field5 = 9
)
select.... from querybce...
No comments:
Post a Comment