COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access
Solution 1
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.
Solution 2
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;
Solution 3
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.
Comments
-
Larry Lustig almost 4 years
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?
-
Cheran Shunmugavel over 12 yearsThe
Amount = 0
part won't work in Access. It should be0 AS Amount
. -
Larry Lustig over 12 yearsYou are correct that I don't want to write my own parser! I have thought about writing my own generator, however, along the lines of what you suggested (but operating on objects, rather than strings). In another part of my application I issue DDL to add new features to the database which has to be specific to the engine; that would benefit from (and be relatively easy to) use a generator. If I did that, I could encapsulate my SELECT logic in different views for each engine. But that's in the future, right now I'm looking for a quick "trick" to handle this particular problem.
-
Larry Lustig over 12 yearsI went with a variant of this solution. My immediate need is to perform an
INSERT. . . SELECT. . .
and I simply repeat the statement twice, once for NULLs and once for the other rows.