SUM(case when ( MYTABLE.STATUS = 'O' or MYTABLE.STATUS = 'C' ) and MYTABLE.TYPE = 'AM' then 1 else 0 end) as OPEN_PM
note single quotes
>Hi,
>
>I don't think SQL Server supports IIF() function. And I need to convert the following expression in my VFP-type SQL Select to SQL Server:
>
>
>sum( iif( ( MYTABLE.STATUS = "O" or MYTABLE.STATUS = "C" ) and MYTABLE.TYPE = "AM", 1, 0 )) as OPEN_PM
>
>
>How do I do it so that it works when passing the expression to SQL Server (via SPT)?
>
>P.S. I forgot to change the default version of SQL Server when creating this message. I am using SQL Server 2005.
>
>UPDATE/SOLUTION: CASE.
javascript:document.Form1.submit()
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.