[thelist] SQL Equivalent to Access Specific Syntax

[Joshua Olson]

----- Original Message ----- 
From: "Rob Smith" <rob.smith at THERMON.com>
Sent: Thursday, September 04, 2003 10:02 AM


> I'm smack dab in the middle of upgrading an Access 97 database and like
> Microsoft, it does many things that no one else does. For example, in
Access
> you have the IIF statement:
>
> IIf([Footage]>=200,IIf([AllocatedBy] Is Null,[footage],0),0) AS
> lengthsmore225

Rob,

CASE...WHEN and Coalesce are your best buddies:

SELECT fields,
Coalesce(CASE WHEN footage >= 200 AND AllocatedBy IS NULL THEN footage END,
0) AS lengthsmore225
FROM whatever

Basically this means "Return footage if both footage >= 200 AND allocatedby
is null, otherwise return 0.

For readibility, you may also want to create a UDF that accepts footage and
allocatedby as parameters and returns the appropriate value:

SELECT fields,
MyNewFunc(footage, AllocatedBy) AS lengthsmore225
FROM whatever

HTH,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168