SQL Equivalent of MAX and IIF

[Will Chamberlain]

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.developersdex.com ***

[getinked]

MAX is the same in both SQL and Access

[Erland Sommarskog]

Will Chamberlain (wi**************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Will Chamberlain]

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

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