Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 at 7:18
    
@AndriyM: You are correct, Sir, thank you. Additionally, the query in parenthesis runs on its own) – MSIS Mar 5 at 6:36
up vote 7 down vote accepted

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;
share|improve this answer
1  
Just curious: why does an alias make such a difference? – MSIS Mar 5 at 6:37

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;
share|improve this answer
    
Good point, will try this. – MSIS Mar 5 at 6:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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