11

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?

Community
  • 1
  • 1
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160

4 Answers4

8

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;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
6

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Stuart Ainsworth
  • 12,792
  • 41
  • 46
  • 1
    The `Amount = 0` part won't work in Access. It should be `0 AS Amount`. – Cheran Shunmugavel Oct 27 '11 at 05:53
  • I 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. – Larry Lustig Oct 31 '11 at 15:30
5

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.

nhahtdh
  • 55,989
  • 15
  • 126
  • 162
Homer T. Nacho Cheese
  • 51
  • 1
  • 1
0

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!...)
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • You 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 Oct 28 '11 at 13:54