By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

SQL Equivalent of MAX and IIF

P: n/a
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 ***
Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
MAX is the same in both SQL and Access

Nov 23 '05 #2

P: n/a
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
Nov 23 '05 #3

P: n/a
Thank you both. I was able to figure it out.

*** Sent via Developersdex http://www.developersdex.com ***
Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.