Join a CTE to a main query

Question

How to I join a CTE to a main query?

(ps: Thank you again - this community is brilliant)

 With tempCTE1locws ([1stchargeableweights], movementref, lastdate)
as 
(Select  
SUM(c.[Chargeable Weight]) AS [1stchargeableweights], 
mm.MovementRef [movementref], 
MAX (ts.systemstamp) [lastdate]

FROM      dbo.whsConsignment wc
INNER JOIN dbo.cgtConsignment c ON c.[Consignment Reference] = wc.ConsignmentReference
INNER JOIN dbo.tsAdditionalColInfo ts on ts.[ConsignmentReference]= wc.ConsignmentReference
inner join dbo.movConLink m on m.ConsignmentReference = c.[Consignment Reference]
inner join dbo.movMovement mm on mm.MovementID = m.MovementID
INNER JOIN dbo.whsHeader wh ON wh.WhsHeaderID = wc.whsHeaderID
AND wc.whsHeaderID = wh.WhsHeaderID
AND wc.StatusCode = 'NL'
AND c.[Service Type] = 'F'
AND ts.SlackNoSlack = 'slack' 
or ts.slacknoslack like 'slack - tripped%'
WHERE wh.ArrDepDate BETWEEN @StartDate AND @EndDate
GROUP by mm.MovementRef),

I would like to join this CTE to dbo.movmovement on movementref. However, writing it like

From dbo.movmovement m 
Inner join dbo.tempCTE1locws locws on locws.movementref = m.movementref

doesn't work at all.

Remove the comma at the end, then use the CTE name as a table. e.g. SELECT * FROM dbo.movmovement AS m INNER JOIN tempCTE1locws AS locws ........ you only get a single use from the CTE though. — Dave Brown, Jun 07 '17 at 11:30
Hi @DaveBrown, thank you for your answer! Following the tempCTE1locws I have another CTE: tempCTE1locwns ([1stchargeableweightns], movementref, lastdate). By deleting the comma at the end, it affects the following CTE. How do I go around this? Thank you again — Jo Jo, Jun 07 '17 at 11:31
@DhruvJoshi Incorrect syntax near 'tempCTE1locwns'. that is the error I am getting - it is the next CTE that I am trying to create. If I add the comma then the error disappears, but the inner join still doesn't work on the basis that tempCTE1locws is an invalid object name. — Jo Jo, Jun 07 '17 at 11:38
Hi - suggested the removal of the comma because that's all the code you posted :) If you have following CTE's, yes the comma is required. See if you can resolve it given the information from @DhruvJoshi. If not, post the entire code and I'll see if I (or anyone else) can assist. Without all the code, we can't know if it's just because you've "consumed" the CTE once and are trying to do it a second time, or if it's something else. — Dave Brown, Jun 07 '17 at 12:21

score 1 · Answer 1 · answered Jun 07 '17 at 11:44

So there's an excellent SO answer on this topic that you should go through. Multiple CTE in single query

Quoting from accepted answer

Use the key word WITH once at the top and if any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top also, even if not all CTEs are recursive:
WITH RECURSIVE
  cte1 AS (...)  -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)

SELECT ... FROM cte3 WHERE ...

This is the reason why you need , in CTE definition.

Your error is coming up because you have a typo.

Incorrect syntax near 'tempCTE1locwns'. that is the error I am getting

See the extra n in CTE name; it should be tempCTE1locwns and not tempCTE1locwns

Hi @DhruvJoshi! It worked brilliantly :) thank you very much for your help :) — Jo Jo, Jun 07 '17 at 12:31
@Diana Please accept my reply as correct answer if it worked for you- so that other users can benefit: from knowing that the answer works and by having the question marked as Answered. — DhruvJoshi, Jun 07 '17 at 14:16

Join a CTE to a main query

1 Answers1