Access DB上的SQL ISNULL()参数数量错误 [英] Wrong number of arguments with SQL ISNULL() on Access DB

问题描述

我在Access DB的VB应用程序中有此查询:

  SELECT DISTINCT Specialization, MAX(a.faultZone) AS faultZone, ISNULL(a.faultCount, 0) AS NoOfFaults  FROM Technicians AS t 
    LEFT JOIN 
             ( 
            SELECT DISTINCT Faults.[Type] AS faultType, MAX(Faults.[Zone]) AS faultZone, COUNT(Faults.[Type]) AS faultCount 
            FROM Faults "
            WHERE Faults.[Zone] = 8 " ' this value will be from variable
            GROUP BY Faults.[Type] "
            ) AS a 
    ON (t.Specialization = a.faultType) 
    WHERE t.specialization <> 'None' "
    GROUP BY a.faultCount, t.Specialization 

它给出了我无法解决的以下问题...

查询表达式中与函数一起使用的参数数量错误 'ISNULL(a.faultCount，0'."

我要实现的只是将NoOFFaults的值设置为零，这意味着在特定区域中没有故障.

谢谢

解决方案

只需加上两美分，虽然我喜欢Nz()的简单语法，但是如果您寻求无故障性能，则IsNull()和NZ( )应避免使用Is Null:
IIF(a.faultCount Is Null, 0, a.faultCount).

在此处查看出色的解释: http://allenbrowne.com/QueryPerfIssue.html

此外，如果您的表位于SQL Server或Oracle中，则使用Nz()将迫使更多查询在本地执行，从而对性能产生巨大影响.

I have this query in VB application on Access DB:

  SELECT DISTINCT Specialization, MAX(a.faultZone) AS faultZone, ISNULL(a.faultCount, 0) AS NoOfFaults  FROM Technicians AS t 
    LEFT JOIN 
             ( 
            SELECT DISTINCT Faults.[Type] AS faultType, MAX(Faults.[Zone]) AS faultZone, COUNT(Faults.[Type]) AS faultCount 
            FROM Faults "
            WHERE Faults.[Zone] = 8 " ' this value will be from variable
            GROUP BY Faults.[Type] "
            ) AS a 
    ON (t.Specialization = a.faultType) 
    WHERE t.specialization <> 'None' "
    GROUP BY a.faultCount, t.Specialization 

It gives following problem that I can't solve...

"Wrong number of arguments used with function in query expression 'ISNULL(a.faultCount, 0'."

What I want to achieve is simply set value of NoOFFaults to zero, which would mean there are no faults in particular Zone.

Thank You

解决方案

Just to add my two cents, and while I like the simple syntax of Nz(), if you seek trouble free performance, both IsNull() and NZ() should be avoided in favor of Is Null:
IIF(a.faultCount Is Null, 0, a.faultCount).

See the excellent explanation here: http://allenbrowne.com/QueryPerfIssue.html

Also, if your tables are in SQL Server or Oracle, using Nz() will force more of the query to be executed locally, with a HUGE performance impact.

这篇关于Access DB上的SQL ISNULL()参数数量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！