MSSQL - Special character syntax

Question

0.00/5 (No votes)

See more:

Hello, I'm fairly new to MSSQL. I have a query where I create 2 CTEs. The second one is then updated using an inner join to the first. This seems to be all working, the problem then occurs when I want to run my final select as the SQL Server Management studio wont' recognize my previous CTE's, suggesting I have the syntax incorrect.

I can post it all, but briefly it is:

;WITH CTE1 AS (Lots of code),
CTE2 AS (Lots of Code)
UPDATE CTE2 some code;

SELECT

At the SELECT the CTEs aren't an option. Should there be some other special character after the CTE2 AS () or something different after the UPDATE statement?

Posted 24-Aug-13 18:34pm

Stu Baby

Updated 24-Aug-13 18:39pm

v2

Add a Solution

Comments

Joezer BH 25-Aug-13 1:45am

I would suggest you also "post it all" so that we can see if there are issues with the query.
Also - what problem are you getting exactly (exception/wrong behaviour/etc)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stu Baby · Answer 1 · 2013-08-24T20:29:00

At the end, it is coming up with a message 'MULTI-PART IDENTIFIER CTE2.STATUS COULD NOT BE BOUND" so I can't more forward as i'm confused as to what I can bind too.

DECLARE @customerId INT,
@supplierId INT,
@subscriptionId INT

SET @customerId = 400 SET @supplierId = 1 SET @subscriptionId = 23471

;WITH CTE1 AS
(
SELECT derivedtbl2.fldSupplierId, derivedtbl2.fldItemCode, derivedtbl2.fldIssueNumber, derivedtbl2.fldShipDate, derivedtbl2.fldStatus, derivedtbl2.fldIssueSequenceNumber,
tblSubscriptionSales_1.fldStatus AS SubStatus, tblSubscriptionSales_1.fldCustomerId
FROM
(SELECT TOP (100) PERCENT derivedtbl_1.fldSupplierId, derivedtbl_1.fldItemCode, tblSupplierData_1.fldIssueNumber, tblSupplierData_1.fldShipDate,
tblSupplierData_1.fldStatus, tblSupplierData_1.fldIssueSequenceNumber
FROM
(SELECT fldSupplierId, fldItemCode
FROM tblSupplierData
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId)
UNION ALL
SELECT fldSupplierId, fldItemCode
FROM tblSubscriptionSales
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId) AND (fldCustomerId = @customerID)) AS derivedtbl_1
INNER JOIN tblSupplierData AS tblSupplierData_1 ON derivedtbl_1.fldSupplierId = tblSupplierData_1.fldSupplierId
AND derivedtbl_1.fldItemCode = tblSupplierData_1.fldItemCode
ORDER BY tblSupplierData_1.fldShipDate DESC) AS derivedtbl2 INNER JOIN
tblSubscriptionSales AS tblSubscriptionSales_1 ON derivedtbl2.fldSupplierId = tblSubscriptionSales_1.fldSupplierId AND
derivedtbl2.fldItemCode = tblSubscriptionSales_1.fldItemCode
GROUP BY derivedtbl2.fldSupplierId, derivedtbl2.fldItemCode, derivedtbl2.fldIssueNumber, derivedtbl2.fldShipDate, derivedtbl2.fldStatus, derivedtbl2.fldIssueSequenceNumber,
tblSubscriptionSales_1.fldStatus, tblSubscriptionSales_1.fldCustomerId
HAVING (tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'A') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'B') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'E') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'F') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'G') OR
(tblSubscriptionSales_1.fldCustomerId = @customerId) AND (tblSubscriptionSales_1.fldStatus = N'P')
),

CTE2 AS
(
SELECT fldIssueNumber, MAX(fldSupplierId) AS Expr1, MAX(fldItemCode) AS Expr2, fldStatus
FROM tblSupplierData
GROUP BY fldIssueNumber, fldSupplierId, fldSubscriptionId, fldStatus
HAVING (fldSubscriptionId = @subscriptionId) AND (fldSupplierId = @supplierId) AND (fldStatus = N'Y')
)

UPDATE CTE2 SET CTE2.fldStatus = 'N' FROM CTE1 INNER JOIN CTE2 ON CTE2.Expr1 = CTE1.fldSupplierId AND CTE2.Expr2 = CTE1.fldItemCode

SELECT tblSupplierData.fldSupplierId, tblSupplierData.fldItemCode
FROM tblSupplierData
WHERE (CTE2.fldStatus <> N'N')
INNER JOIN CTE2 AS tblSupplierData.fldSupplierId = CTE2.fldSupplierId AND tblSupplierData.fldItemCode = CTE2.fldItemCode