patternsqlMinor
Correlated Subquery SQL Server 2014
Viewed 0 times
sqlcorrelatedsubqueryserver2014
Problem
I am (unsuccessfully) using a correlated subquery on an Invoices table:
to find the sum of largest unpaid invoices by all vendors, where the unpaid condition is given by
The inner query runs , which is good and bad, since the query is supposed
to be correlated , but the query as a whole does not run, and I get the error message:
What am I doing wrong?
Invoices(InvoiceID, VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ),to find the sum of largest unpaid invoices by all vendors, where the unpaid condition is given by
InvoiceTotal-PaymentTotal-CreditTotal <0: Select Sum(LargestUnpaid) from
(Select Max(InvoiceTotal) AS LargestUnpaid from Invoices
where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) ;The inner query runs , which is good and bad, since the query is supposed
to be correlated , but the query as a whole does not run, and I get the error message:
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.What am I doing wrong?
Solution
Add an alias for the derived table before the
; for example VendLargestUnpaidInv:Select Sum(LargestUnpaid) from
(Select Max(InvoiceTotal) AS LargestUnpaid from Invoices
where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;Code Snippets
Select Sum(LargestUnpaid) from
(Select Max(InvoiceTotal) AS LargestUnpaid from Invoices
where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;Context
StackExchange Database Administrators Q#130866, answer score: 8
Revisions (0)
No revisions yet.