可以在SQL Server和MS Access中使用的COALESCE，IFNULL或NZ（）函数

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:

我有一个项目，可以使用SQL Server或MS Access作为数据存储。在一个SELECT语句中，我必须对单个列和单个值执行COALESCE操作，如下所示：

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.

我想编写一个SQL语句，它将在SQL Server和MS Access中正确执行。直接感兴趣的SQL Server版本是2008，尽管适用于各版本的解决方案将是首选。

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?

今天早些时候，有人能够向我展示一个SQL技巧，它允许我使用单个SELECT语句来有效地将DATETIME CAST到DATE。我想知道是否有人有类似的技巧以可以应用于SQL Server和MS Access的方式执行COALESCE（例如，IFNULL或NZ）操作？

4 个解决方案

#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.

显然，如果您有多个列，这将很快无法管理。

#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.

注意Nz（）仅在使用Access用户界面时可用。

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

以下是一些可以很容易地转换为COALESCE的建议，尽管重复列很痛苦：

Sample 1:

样本1：

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

Sample 2:

样本2：

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

#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.

现在，您将能够在MS Access和SQL中使用COALESCE函数。

#4

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

我想你不想写一个解析器来管理Jet SQL和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.

我们开发的解决方案（是的，我们有一个类似的问题需要解决）是定义我们在meta-SQL语法中使用的一些“伪元语言”，并且我们有一种从这种元语言到Jet SQL的翻译器或者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
我们不是在Jet和T-SQL之间进行翻译，而是从“元语法”中进行翻译。它使事情变得容易多了
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!...)
我们的元语法依赖于相应的字符串（如'@ MyWildCard @'或'@ CarSep @'）特定于我们的语法，并且不能用作数据值（否则我们必须管理一些'meta-注射'风险！......）

#1