Microsoft SQL Server forum - SQL Equivalent of MAX and IIF

Author

SQL Equivalent of MAX and IIF

Will Chamberlain

2005-11-21, 8:24 pm

I have looked around and found the equivalent for IIF (Access) to be a
SELECT CASE in SQL. 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:

SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN FROM ADCN
INNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID WHERE (Sheet.Drawing
= '" & x & "') AND (Sheet.SheetNumber = 0);

This portion is the most important:
SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN

*** Sent via Developersdex http://www.droptable.com ***

getinked

2005-11-21, 8:24 pm

MAX is the same in both SQL and Access

Erland Sommarskog

2005-11-21, 8:24 pm

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Will Chamberlain

2005-11-21, 8:24 pm

Thank you both. I was able to figure it out.

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links