What's wrong with my SQL query? Syntax errors

Question

This SQL query worked fine until I tried adding the CASE statement in, but now I get syntax errors as described at the bottom of this post. Here's the SQL (pretty epic, but the syntax errors take place around the CASE, maybe with the SageAccount select prior)

SELECT CurrentBalance.Value AS CurrentBalanceValue, 
       Debt30Balance.Value AS Debt30BalanceValue, 
       Debt60Balance.Value AS Debt60BalanceValue, 
       Debt90Balance.Value AS Debt90BalanceValue, 
       WIP.Value AS WIPValue, CurrentBalance.Value + Debt30Balance.Value + 
           Debt60Balance.Value + Debt90Balance.Value AS SLDebt,  
       CurrentBalance.Value + Debt30Balance.Value + Debt60Balance.Value + 
           Debt90Balance.Value+ WIP.Value AS TotalDebt,
       (SELECT CreditTerms FROM SageAccount WHERE ID=@CustomerID) 
       AS CreditTerms,
CASE WHEN CreditTerms <= 30 THEN
CurrentBalance.Value + Debt30Balance.Value + 
    Debt60Balance.Value + Dept90Balance.Value
ELSE WHEN CreditTerms <= 60 THEN
Debt30Balance.Value + Debt60Balance.Value + Dept90Balance.Value
ELSE WHEN CreditTerms <= 90 THEN
Debt60Balance.Value + Dept90Balance.Value
ELSE
Debt90Balance.Value
END AS Overdue
FROM (SELECT TOP (1) Value, Customer FROM DebtBreakdown 
       WHERE (Type = 0) AND (Customer = @CustomerID) 
       ORDER BY Timestamp DESC) AS CurrentBalance 
       INNER JOIN (SELECT TOP (1) Value, Customer 
       FROM DebtBreakdown AS DebtBreakdown_1 
       WHERE (Type = 1) AND (Customer = @CustomerID) 
       ORDER BY Timestamp DESC) AS Debt30Balance ON 
       CurrentBalance.Customer = Debt30Balance.Customer 
       INNER JOIN (SELECT TOP (1) Value, Customer FROM 
       DebtBreakdown AS DebtBreakdown_2 
       WHERE (Type = 2) AND (Customer = @CustomerID) 
       ORDER BY Timestamp DESC) 
       AS Debt60Balance ON Debt30Balance.Customer = Debt60Balance.Customer 
       INNER JOIN (SELECT TOP (1) Value, Customer FROM DebtBreakdown AS 
       DebtBreakdown_3 WHERE (Type = 3) AND (Customer = @CustomerID) 
       ORDER BY Timestamp DESC) AS Debt90Balance ON 
       Debt60Balance.Customer = Debt90Balance.Customer 
       INNER JOIN (SELECT TOP (1) Value, Customer FROM DebtBreakdown AS 
       DebtBreakdown_4 WHERE (Type = 4) AND (Customer = @CustomerID) 
       ORDER BY Timestamp DESC) AS WIP ON Debt90Balance.Customer = WIP.Customer

Sorry, forgot the errors!

Error in WHERE clause near 'WHEN'. Error in WHERE clause near ','. Error in WHERE clause near 'ORDER'. Unable to parse query text.

What's the error you're getting? I see nothing at the bottom of your post about errors. — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Oct 12, 2010 at 16:15

AdaTheDev · Accepted Answer · 2010-10-12 16:16:42Z

10

Try changing the "ELSE WHEN" statements to just "WHEN" (leave the final ELSE as-is)

answered Oct 12, 2010 at 16:16

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LittleBobbyTables - Au Revoir Over a year ago

+1 - That's what I get for trying to format his question - beat me to it.

Collectives™ on Stack Overflow

What's wrong with my SQL query? Syntax errors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related