trishhfernandez avatar image
trishhfernandez asked

Join CTE temp table to derived table

How can I join my CTE table from a derived table? as I am getting an "Incorrect Syntax error near )" Error in join expression, unable to parse query (SELECT TOP (100) PERCENT ItemSpecs_1.ItemID, SUM(SalesOrderDetails_1.QtyOrdered * ItemSpecFullStruc_1.TotalQtyPerRoot) AS SalesQTY FROM dbo.SalesOrderDetails AS SalesOrderDetails_1 INNER JOIN dbo.ItemSpecFullStruc AS ItemSpecFullStruc_1 ON SalesOrderDetails_1.ItemSpecID = ItemSpecFullStruc_1.RootItemSpecID INNER JOIN dbo.ItemSpecs AS ItemSpecs_1 ON ItemSpecFullStruc_1.ChildItemSpecID = ItemSpecs_1.ItemSpecID INNER JOIN dbo.SalesOrder ON SalesOrderDetails_1.SalesOrderID = dbo.SalesOrder.SalesOrderID WHERE (SalesOrderDetails_1.SOStatus < 6) AND (dbo.SalesOrder.EntryDate > GETDATE() - 84) GROUP BY ItemSpecs_1.ItemID ORDER BY ItemSpecs_1.ItemID) AS [12WeekSales] ON dbo.Items.ItemID = [12WeekSales].ItemID LEFT OUTER JOIN and this is my CTE table ;WITH mytemp AS (SELECT TOP (100) PERCENT ItemSpecs_2.ItemNo, WorkOrderDetails.QtyComplete * ItemSpecFullStruc_2.TotalQtyPerRoot AS QtyComp FROM dbo.WorkOrderDetails AS WorkOrderDetails INNER JOIN dbo.ItemSpecFullStruc AS ItemSpecFullStruc_2 ON ItemSpecFullStruc_2.RootItemSpecID = WorkOrderDetails.ItemSpecID INNER JOIN dbo.ItemSpecs AS ItemSpecs_2 ON ItemSpecs_2.ItemSpecID = ItemSpecFullStruc_2.ChildItemSpecID INNER JOIN dbo.WorkOrder AS WorkOrder_1 ON WorkOrder_1.WorkOrderID = WorkOrderDetails.WorkOrderID LEFT OUTER JOIN dbo.ToBeScheduled_Completed ON WorkOrder_1.WorkOrderID = dbo.ToBeScheduled_Completed.WorkOrderID WHERE (WorkOrderDetails.Completed = 1) AND (WorkOrderDetails.CompDate > GETDATE() - 42) GROUP BY ItemSpecs_2.ItemNo, WorkOrderDetails.QtyOrdered, ItemSpecFullStruc_2.TotalQtyPerRoot, WorkOrderDetails.[LineNo], WorkOrderDetails.QtyComplete, WorkOrderDetails.CompDate, WorkOrderDetails.QtyComplete * ItemSpecFullStruc_2.TotalQtyPerRoot) SELECT ItemNo, SUM(QtyComp) AS WorkQTY FROM mytemp AS WOSold GROUP BY ItemNo ) AS WOSold ON dbo.Items.ItemID = WOSold.ItemID LEFT OUTER JOIN ItemNo QtyCom 7441 9
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

trishhfernandez avatar image trishhfernandez commented ·
this is SQL Server
0 Likes 0 ·

1 Answer

ThomasRushton avatar image
ThomasRushton answered
You treat a CTE just like any other table, with the proviso that it exists only for that one statement. Something like: WITH myCTE AS () SELECT * FROM myCTE INNER JOIN () AS q2 ON myCTE.something = q2.something (Obviously, untested...)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.