Need Help with CASE Statement

[Cheryl3D]

Hi:

I need help with a CASE statement in SQL Server 2000. I am converting an Access 2000 database to SQL Server 2000. In Access 2000, as you know you can use the IIF (Immediate IF). I need to replace the following line of code that uses the Immediate IF:

(IIf([RCost]>0,-1,0)) AS RB,

Here is the complete code for the entire view that I tried in SQL Server 2000:

SELECT DISTINCT qryAnnex_View.[ID #],
qryAnnex_View.SRAN,
qryAnnex_View.JULIAN,
qryAnnex_View.Supplier,
qryAnnex_View.Receiver,
CASE qryAnnex_View.RCost
WHEN qryAnnex_View.RCost > '0' THEN '1'
ELSE '0'
END AS RB,
-- (IIf([RCost]>0,-1,0)) AS RB,
Sum(qryAnnex_View.RCost) AS SumOfRCost,
Sum(([NRCost]+[NRWorkyearCost])) AS NRC,
qryAnnex_View.Annex_Complete, DateAdd(y,365,[Annex_Complete]) AS [Annual Review Due],
qryAnnex_View.Status
FROM qryAnnex_View
GROUP BY qryAnnex_View.[ID #],
qryAnnex_View.SRAN,
qryAnnex_View.JULIAN,
qryAnnex_View.Supplier,
qryAnnex_View.Receiver,
Coalesce(RCost>0,-1,0),
-- (IIf([RCost]>0,-1,0)),
qryAnnex_View.Annex_Complete,
DateAdd(y,365,[Annex_Complete]),
qryAnnex_View.Status
HAVING DateAdd(y,365,[Annex_Complete]) Between DateAdd(m,1,GETDATE() And DateAdd(m,3,GETDATE()) AND ((qryAnnex_View.Status) Not Like 'Ter*' And (qryAnnex_View.Status) Not Like 'Sup*'))
ORDER BY DateAdd(y,365,[Annex_Complete])


When I tried the SQL statement above, I only received the following error message in SQL Server:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '>'.

I tried using the COALESCE function, but to no avail. How can I make the Immediate IF statement below work for the view?

(IIf([RCost]>0,-1,0)) AS RB,

Any help is greatly appreciated.

Thanks,
CherylD

 

[JamesLean]

The correct CASE statement for that is:

CASE WHEN rcost > 0 THEN -1 ELSE 0 END AS rb

You need to use that in your select statement and again in your GROUP BY clause (except don't include the alias there).

--James

 

[VogonPoet]

Cheryl3D,

I think your case statement should look like the following:

CASE WHEN qryAnnex_View.RCost > 0 THEN 1 ELSE 0 END

I'm assuming RCost is a numeric value and not a text value (your Access statement was structured that way).

Michael

 

[Cheryl3D]

James and Michael:

Thanks very much, both worked. Also, Michael you were correct in your assumption, RCost is a numeric value(real).

CheryD