What is NVL & CASE WHEN

[AFSSkier]

I have a calculation I'm trying to replacate in MS-Access from Informix.
What is NVL & CASE WHEN?

Informix
NVL(((NVL(pa12.sales_case_units, 0) - NVL(pa14.sales_doll, 0)) / (CASE WHEN
pa12.sales_case_units = 0 THEN NULL ELSE pa12.sales_case_units END)), 0)
WJXBFS7,

 

[Dirk Goldgar]

I have a calculation I'm trying to replacate in MS-Access from Informix.
What is NVL & CASE WHEN?

Informix
NVL(((NVL(pa12.sales_case_units, 0) - NVL(pa14.sales_doll, 0)) / (CASE
WHEN
pa12.sales_case_units = 0 THEN NULL ELSE pa12.sales_case_units END)), 0)
WJXBFS7,

I don't know Informix, but from the usage it looks as though NVL() may be
equivalent to Nz(), and the IIf() function may be used to implement the CASE
WHEN structure. IIf() is not exactly the same as a CASE structure, though.
You can implement a more complex case structure by nesting IIf() functions.
There is also a VB Switch() function that is similar to a case structure,
and it can be called from an Access query; however, it is not native to Jet
SQL and therefore is less efficient that IIf().

 

[a a r o n . k e m p f]

you really should move from informix to SQL Server, not from one
obsolete database to another.

learn a real ETL tool.
and _KEEP_ the case when then it is 1000000 times more powerful than
nested IF / IIF

-Aaron

 

[a a r o n . k e m p f]

good stuff

MDBs haven't been updated in several versions of Access and thus are
dead end technology.

Aaron