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

Question

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

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

我想写一个SQL语句，将在SQL Server和MS访问正确执行。直接感兴趣的SQL Server版本是2008，尽管最好是一个跨版本适用的解决方案。

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

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@')是特定于我们的语法的，不能用作数据值(否则我们将不得不管理一些'元注入'风险!…)