>I'm getting "Incorrect syntax near the keyword 'THEN'.". Anyone know what I'm doing wriong here?
>
>
>
>SELECT *,
> CASE
>
> WHEN PAY_REC = 'REC' AND (NOTSHIP > CASH_BAL OR (NOTSHIP < CASH_BAL AND CASH_BAL > 0)
> THEN CASH_BAL - NOTSHIP
> ELSE 0
>
> WHEN PAY_REC = 'PAY' AND (NOTSHIP > CASH_BAL OR (NOTSHIP < CASH_BAL AND CASH_BAL > 0)
> THEN CASH_BAL
> ELSE 0
>
> END AS Ar
>
>FROM Trades
>
You are allowed for only one ELSE in the one CASE statement:
In VFP you can have only one OTHERWISE, C++ only one default: etc.
CASE
WHEN PAY_REC = 'REC' AND (NOTSHIP > CASH_BAL OR (NOTSHIP < CASH_BAL AND CASH_BAL > 0)
THEN CASH_BAL - NOTSHIP
WHEN PAY_REC = 'PAY' AND (NOTSHIP > CASH_BAL OR (NOTSHIP < CASH_BAL AND CASH_BAL > 0)
THEN CASH_BAL
ELSE 0
END AS Ar
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.