CTE Expression Giving Error Did Not Figure Out Why

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CTE Expression Giving Error Did Not Figure Out Why

Author

Topic

mindtrap
Starting Member

10 Posts

Posted - 2011-07-05 : 06:25:45

Try to find which vendor from each state have largest invoice


WITH [Summary] AS
(
SELECT VendorState, VendorName, SUM(InvoiceTotal) AS TotalInvoice
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendors.VendorState, Vendors.VendorName
);
WITH [Total] AS
(
SELECT VendorState, MAX(TotalInvoice) AS TotalInvoice
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorState
)
SELECT [Summary].VendorState,Summary.VendorName,[Total].TotalInvoice 
FROM [Summary] JOIN [Total]
ON
[Summary].VendorState = [Total].VendorState
AND [Summary].TotalInvoice = [Total].TotalInvoice
ORDER BY Summary.VendorState

But the code above constantl giving error

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

All help will be appreciated and thanks in advance

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-05 : 06:31:22

See below:

quote:
Originally posted by mindtrap

Try to find which vendor from each state have largest invoice

;WITH [Summary] AS
(
SELECT VendorState, VendorName, SUM(InvoiceTotal) AS TotalInvoice
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendors.VendorState, Vendors.VendorName
);,
~~WITH~~ [Total] AS
(
SELECT VendorState, MAX(TotalInvoice) AS TotalInvoice
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorState
)
SELECT [Summary].VendorState,Summary.VendorName,[Total].TotalInvoice
FROM [Summary] JOIN [Total]
ON
[Summary].VendorState = [Total].VendorState
AND [Summary].TotalInvoice = [Total].TotalInvoice
ORDER BY Summary.VendorState

But the code above constantl giving error

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

All help will be appreciated and thanks in advance

Corey

I Has Returned!!

mindtrap
Starting Member

10 Posts

Posted - 2011-07-05 : 06:39:01

Sir really thanks for your help I was about to lose the plot :)


;WITH [Summary] AS
(
SELECT VendorState, VendorName, SUM(InvoiceTotal) AS TotalInvoice
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY Vendors.VendorState, Vendors.VendorName
),
[Total] AS
(
SELECT VendorState, MAX(Summary.TotalInvoice) AS TotalInvoice
FROM Summary
GROUP BY VendorState
)
SELECT [Summary].VendorState,Summary.VendorName,[Total].TotalInvoice 
FROM [Summary] JOIN [Total]
ON
[Summary].VendorState = [Total].VendorState
AND [Summary].TotalInvoice = [Total].TotalInvoice
ORDER BY Summary.VendorState

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources