vb.net Access DB 上的 SQL ISNULL() 参数数量错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13633980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Wrong number of arguments with SQL ISNULL() on Access DB
提问by JanT
I have this query in VB application 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
It gives following problem that I can't solve...
它给出了我无法解决的以下问题......
"Wrong number of arguments used with function in query expression 'ISNULL(a.faultCount, 0'."
“在查询表达式‘ISNULL(a.faultCount, 0’)中与函数一起使用的参数数量错误。”
What I want to achieve is simply set value of NoOFFaultsto zero, which would mean there are no faults in particular Zone.
我想要实现的只是将 的值设置NoOFFaults为零，这意味着特定区域没有故障。
Thank You
谢谢你
回答by Patrick Honorez
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).
只是为了增加我的两分钱，虽然我喜欢 Nz() 的简单语法，但如果您寻求无故障的性能，则应避免使用 IsNull() 和 NZ() 以支持 Is Null: IIF(a.faultCount Is Null, 0, a.faultCount)。
See the excellent explanation here: http://allenbrowne.com/QueryPerfIssue.html
请参阅此处的出色解释：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.
此外，如果您的表位于 SQL Server 或 Oracle 中，则使用 Nz() 将强制在本地执行更多查询，从而对性能产生巨大影响。
回答by LittleBobbyTables - Au Revtheitroad
Microsoft Access' version of IsNullis different than most SQL versions; it simply returns TRUE if the value is NULL, and FALSE if it isn't.
Microsoft Access 的版本与IsNull大多数 SQL 版本不同；简单地说，如果该值为NULL，假如果不是返回TRUE。
You need to basically build your own using IIF():
您需要基本上使用IIF()以下方法构建自己的：
IIF(ISNULL(a.faultCount), 0, a.faultCount)
回答by HelloW
I think that you are looking for the nz function
我认为您正在寻找 nz 功能
Nz(a.faultCount, 0)
will return 0 if the value is null
如果值为 null 将返回 0
