1

I'm unfortunately dealing with someone else's code here, but needing a little push.

What I have happening is that I have a CTE that is needed to run in a nested join.

...Start of CTE
INNER JOIN (
;WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange 
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
)
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) AS WipDet ON InvMaster.StockCode = WipDet.StockCode   ...

I've tried removing the semi-colon in front of the CTE, but then get a syntax error stating it is expecting that the previous statement must be terminated with a semi-colon.

I beating my head against the keyboard on this one.

closed as off-topic by Max Vernon, RLF, Michael Green, Mark Sinkinson, Colin 't Hart May 5 '15 at 8:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RLF, Michael Green, Mark Sinkinson, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

3

This sounds like dynamic SQL where you're constructing a query from other subqueries...

The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML.

So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ORDER BY, so that's good.

You could find it useful to make WipDet a CTE too... Like this:

WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange 
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
),
WipDet AS (
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) 
SELECT ...
FROM InvMaster
INNER JOIN WipDet 
ON InvMaster.StockCode = WipDet.StockCode 

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