Posted by Erland Sommarskog on 11/22/05 01:05

Will Chamberlain (will.chamberlain@devdex.com) writes:
> I have looked around and found the equivalent for IIF (Access) to be a
> SELECT CASE in SQL.

SELECT IsThisTrue = CASE WHEN <somecondition> THEN 1 ELSE 0 END

> I have tried this with no success. I am also looking
> for the equivalent of MAX and have had no luck. The portion of the
> string I am trying to SQL'ize is:

For MAX you use CASE as well:

SELECT maxval = CASE WHEN col1 > col2 THEN col1 ELSE col2 END

If it's MAX of two column values. For an aggregate, it's MAX in
SQL Server as well.

> SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN FROM ADCN

For "IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN]" there is a shortcut in
coalesce:

coalesce([ADCN.ADCN], 0)

coalesce accept a list of values and returns the first non-NULL value.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: SQL Equivalent of MAX and IIF
• Prev in forum: Re: Need help with Count function and temporary tables
• Thread view: Re: SQL Equivalent of MAX and IIF

[Reply to this message]