I am having difficulties translating Access queries into
SQL. Specifically with those that include functions like IIf.
I know this is where you use CASE, but can anyone shed light on
a conversion of the following query statement?

CoreCalc: IIf(IsNull([Core]),0,IIf([UOS]=0,0,([Core]*80/[UOS])))



CEJ

__________________
CEJ

Try:
frankly I think that's a lot easier to read and understand...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

A LOT EASIER INDEED!

The orgininal statement was written by someone who had
no experience programming with SQL only the Access Query Builder


CEJ

Last example I promise:

This has an IIf within an IIf

IIf([FTester]="True",IIf(nz([UOS])=0,0,(([FFTEs]*80*([HrsProd]/[HrsPaid]))/[UOS])),Null) AS FTgtProdHrsperUOS,



CEJ

You ought to be able to figure these out yourself, now, as you've seen how to translate an IIF expression into a CASE expression. In this current example, you'll simply have one CASE expression embedded within another. The only other difference here is the presence of the NZ function which returns a 0 if the argument is a null (or a zero length string if the argument is a string datatype).

I question the datatype of 'FTester'. I don't think this is a string with the value 'True'; instead I suspect this is a bit column (or maybe a numeric datatype) with the value of 0 or 1 corresponding to false or true respectively. I'll assume that:
Here I use the COALESCE function. This function returns the first non-null value in its list of arguments. Thus, this has the value of the column UOS if the UOS column is not null, otherwise it has the value 0.

Note also that you are not protecting against a possible divide by zero if 'HrsPaid' is 0. Guarding against that is left as an exercise to the reader. ;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

You are amazingly perceptive. I did get an error on the Ftester column. In fact it said the column didn't exist. However, Ftester
was a column created in the CASE statement prior to the last mentioned.

SELECT
     CASE WHEN FFTEs IS NULL THEN 0 ELSE -1 END AS Ftester
     CASE WHEN Ftester=1 THEN
          CASE WHEN coalesce(UOS,0)=0 then 0
          ELSE (FFTEs*80*(HrsProd/HrsPaid))/UOS
          END
          ELSE NULL END AS FTgtProdHrsperUOS





CEJ

Column aliases come into existence all at once, after all the other clauses (such as FROM, WHERE, and GROUP BY (but not ORDER BY)) have been executed. Thus, you cannot refer to an aliased column in these clauses, or in the SELECT itself.

It's a bummer, but that's the way it is.

You have no choice but to repeat the expression that defines Ftester, although you can simplify things a bit:

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com