7

I am (unsuccessfully) using a correlated subquery on an Invoices table:

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?

  • 6
    Just FYI, you are not using a correlated subquery there. That is called a derived table. A subquery is called correlated if it references any of the table aliases found in the [outer query's] FROM clause and is located in any of these parts of the [outer] query: the SELECT clause, an APPLY operator, the WHERE clause, the ON subclause of a join. – Andriy M Mar 1 '16 at 7:18
  • @AndriyM: You are correct, Sir, thank you. Additionally, the query in parenthesis runs on its own) – MSIS Mar 5 '16 at 6:36
7

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;
  • 1
    Just curious: why does an alias make such a difference? – MSIS Mar 5 '16 at 6:37
3

Or to make it more readable, using CTE (Common Table Expression)

With VendLargestUnpaidInv as 
( 
    Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices 
    where InvoiceTotal - (PaymentTotal+CreditTotal)<0 
    group by vendorID
)
select sum(LargestUnpaid) 
from VendLargestUnpaidInv;
  • Good point, will try this. – MSIS Mar 5 '16 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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