iif and case statements

Hi all,

I have to translate an Access query into sql. The query has the
following statement. I know SQL doesn't support iif, so can someone tell
me how to use the case statement to get the same result?

select field1,
IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
field3

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!CASE WHEN grand_total>50 THEN grand_total-50 ELSE 0 END

--
David Portas
----
Please reply only to the newsgroup
--

"Hammy Hammy" <chris@.thehams.ca> wrote in message
news:3f5e54ee$0$62085$75868355@.news.frii.net...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hammy Hammy <chris@.thehams.ca> wrote in message news:<3f5e54ee$0$62085$75868355@.news.frii.net>...
> Hi all,
> I have to translate an Access query into sql. The query has the
> following statement. I know SQL doesn't support iif, so can someone tell
> me how to use the case statement to get the same result?
> select field1,
> IIf(Grand_total-50>0, grand_total-50, 0) AS field2,
> field3
> Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

select field1,
case when grand_total > 50 then grand_total - 50 else 0 end as 'field2',
field 3
from ...

Simon