SQL SERVER CASE STATEMENTS 1

[Abracadabra]

I am in the process of converting some Access queries containing Iif expressions to SQL Server. I have already received helpful advice from a thread I posted, advising me to use the Case statement instead. This works very well, but I now having problems with multiple instances of Case within the same query. The query below works OK with just the one Case statement, but I get a syntax error now I have added the second case statement starting on line 12. Any advice much appreciated.




Alter Procedure SP1
AS
SELECT PI.[Invoice number],
PI.[Cust account no] AS [account code], PI.[product code],
PI.[product location] AS [warehouse code],
CASE [sale or return]
WHEN 'R'
THEN - PI.[movement qty]
ELSE PI.[movement qty]
END AS quantity
CASE [sale or return]
WHEN 'R'
THEN - PI.[movement value]
ELSE PI.[movement value]
END AS value
FROM AA INNER JOIN
PI ON AA.[Account no] = PI.[Cust account no] INNER JOIN
DA ON PI.[Cust account no] = DA.[Account number]
WHERE (PI.[product code] BETWEEN N'010000' AND N'999999')

 

[StevenK]

Unless the SQL syntax a listed contains a typo from your version used then the error may be the requiring of a comma after the line 'END AS quantity'.
Is the answer as simple as this ?
Steve