可在 SQL Server 和 MS Access 中使用的 COALESCE、IFNULL 或 NZ() 函数

Question

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

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

我想编写一条可以在 SQL Server 和 MS Access 中正确执行的 SQL 语句。最受关注的 SQL Server 版本是 2008，不过跨版本适用的解决方案将是首选。

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

Answer 1

我认为没有任何语法在两个平台上具有相同的功能。

注意 Nz() 仅在使用 Access 用户界面时可用。

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

示例 1：

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

示例 2：

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

Answer 2

这会起作用，但很笨重：

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

显然，如果您有多个列，这很快就会变得难以管理。

Answer 3

在模块中创建自定义公共函数。

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

添加错误处理等，进行改进。

现在，您可以在 MS Access 和 SQL 中使用 COALESCE 函数。

Answer 4

我猜你不想编写一个解析器来管理 Jet SQL 和 T-SQL 之间的翻译...

我们开发的一个解决方案（是的，我们有一个类似的问题需要解决）是定义一些“伪元语言”我们在元 SQL 语法中使用它，并且我们有一种从这种元语言到 Jet SQL 或 T-SQL 的转换器。

示例：

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;"

相同的策略可用于通配符和分隔符：

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%""

我知道它不是那么好，但它非常高效和干净。要点是：

• 我们不是在 Jet 和 T-SQL 之间进行翻译，而是从“元语法”进行翻译。它使事情变得容易很多，
• 当函数没有相同数量的参数或参数未按相同顺序传递时，应该非常小心。它仍然可以完成......
• 我们的元语法依赖于这样一个事实：相应的字符串（如“@MyWildCard@”或“@CarSep@”）特定于我们的语法，并且不能用作数据值（否则我们必须管理一些“元注入”风险！...）