COALESCE, IFNULL, or NZ() function that can be use

Question

I have a project that can use either SQL Server or MS Access as the data store. In one SELECT statement, I must perform a COALESCE operation on a single column and a single value, like this:

SELECT COALESCE([Amount], 0) FROM PaymentsDue;

I would like to write a single SQL statement that will execute correctly in both SQL Server and MS Access. The SQL Server version that is of immediate interest is 2008, although a solution applicable across versions would be preferred.

Earlier today, someone was able to show me an SQL trick that allowed me to use a single SELECT statement to effectively CAST a DATETIME to DATE. I was wondering if anyone has a similar trick to perform a COALESCE (eg, IFNULL or NZ) operation in a way that can be applied to both SQL Server and MS Access?

Answer 1

I don't think there is any syntax that functions the same on both platforms.

Note Nz() is only available when using the Access user interface.

Here are a couple of suggestions that can be transformed to COALESCE fairly easily, though repeating the column is a pain:

Sample 1:

SELECT IIF([Amount] IS NULL, 0, [Amount]) FROM PaymentsDue;

Sample 2:

SELECT SWITCH([Amount] IS NULL, 0, TRUE, [Amount]) FROM PaymentsDue;

Answer 2

Create a custom public function in a module.

Public Function COALESCE(InputValue, ValueIfNull)
   COALESCE = nz(InputValue, ValueIfNull)
End Function

Add in error handling, etc., make improvements.

Now, you would be able to use the COALESCE function in MS Access and SQL.

Answer 3

And I guess you do not want to write a parser that will manage translations between Jet SQL and T-SQL ...

A solution that we developped (yes, we had a similar problem to solve) is to define some 'pseudo-metalanguage' that we use in our meta-SQL syntax, and we have a kind of translator from this meta-language into Jet SQL or T-SQL.

Example:

myQuery = "SELECT @MyCoalesceFunction@([Amount], 0) FROM PaymentsDue;"

myQuery = convertFromMeta(myQuery,"T-SQL")
will give
    "SELECT COALESCE([Amount], 0) FROM PaymentsDue;"

myQuery = convertFromMeta(myQuery,"JET-SQL")
will give
    "SELECT NZ([Amount], 0) FROM PaymentsDue;"

The same strategy could be used for wildcards and delimiters:

myQuery = "SELECT [Amount] FROM PaymentsDue WHERE id_client LIKE @CarSep@ABC@MyWildCard@@CarSep@"

myQuery = convertFromMeta(myQuery,"T-SQL")
will give
    "SELECT [Amount] FROM PaymentsDue  WHERE id_client LIKE 'ABC%'"

myQuery = convertFromMeta(myQuery,"JET-SQL")
will give
    "SELECT [Amount] FROM PaymentsDue  WHERE id_client LIKE "ABC%""

I konw it's not that nice, but it is quite efficient and clean. The main points are:

• We are not translating between Jet and T-SQL, but from a 'meta-syntax'. It makes things a lot easier
• One should be very careful when functions do not have the same number of parameters, or when parameters are not passed in the same order. It still can be done ...
• Our meta-syntax relies on the fact that the corresponding strings (like '@MyWildCard@' or '@CarSep@') are specific to our syntax, and cannot be used as data values (otherwise we would have to manage some 'meta-injection' risks!...)

Answer 4

This will work, but it's clunky:

SELECT Amount 
FROM PaymentsDue
WHERE Amount IS NOT NULL
UNION ALL
SELECT 0 AS Amount 
FROM PaymentsDue
WHERE Amount IS NULL

Obviously if you have more than one column, this gets to be quickly unmanageable.