MySQL 调用本机函数“ISNULL”时参数计数不正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21380956/
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
Incorrect parameter count in the call to native function 'ISNULL'
提问by Kinyanjui Kamau
I have a query that I am trying to convert to MySQL from MS SQL Server 2008. It runs fine on MSSQL,
I get the error
"Incorrect parameter count in the call to native function 'ISNULL'".
我有一个查询，我试图将其从 MS SQL Server 2008 转换为 MySQL。它在 MSSQL 上运行良好，但出现错误“ Incorrect parameter count in the call to native function 'ISNULL'”。
How do I solve this?
我该如何解决这个问题？
SELECT DISTINCT
dbo.`@EIM_PROCESS_DATA`.U_Tax_year,
dbo.`@EIM_PROCESS_DATA`.U_Employee_ID,
CASE
WHEN dbo.`@EIM_PROCESS_DATA`.U_PD_code = 'SYS033' THEN SUM(dbo.`@EIM_PROCESS_DATA`.U_Amount)
END AS PAYE,
CASE
WHEN dbo.`@EIM_PROCESS_DATA`.U_PD_code = 'SYS014' THEN SUM(dbo.`@EIM_PROCESS_DATA`.U_Amount)
END AS TOTALTAXABLE,
dbo.OADM.CompnyName,
dbo.OADM.CompnyAddr,
dbo.OADM.TaxIdNum,
dbo.OHEM.lastName + ', ' + ISNULL(dbo.OHEM.middleName, '') + '' + ISNULL(dbo.OHEM.firstName, '') AS EmployeeName
FROM
dbo.`@EIM_PROCESS_DATA`
INNER JOIN
dbo.OHEM ON dbo.`@EIM_PROCESS_DATA`.U_Employee_ID = dbo.OHEM.empID
CROSS JOIN
dbo.OADM
GROUP BY dbo.`@EIM_PROCESS_DATA`.U_Tax_year , dbo.`@EIM_PROCESS_DATA`.U_Employee_ID , dbo.OADM.CompnyName , dbo.OADM.CompnyAddr , dbo.OADM.TaxIdNum , dbo.OHEM.lastName , dbo.OHEM.firstName , dbo.OHEM.middleName , dbo.`@EIM_PROCESS_DATA`.U_PD_code
MySQL
MySQL
SELECT DISTINCT
processdata.taxYear, processdata.empID,
CASE WHEN processdata.edCode = 'SYS033' THEN SUM (processdata.amount) END AS PAYE,
CASE WHEN processdata.edCode = 'SYS014' THEN SUM (processdata.amount) END AS TOTALTAXABLE,
company.companyName, company.streetAddress, company.companyPIN, employeemaster.lastName + ', ' + IFNULL(employeemaster.middleName, '')
+ ' ' + IFNULL(employeemaster.firstName, '') AS EmployeeName
FROM
processdata INNER JOIN
employeemaster ON processdata.empID = employeemaster.empID
CROSS JOIN company
GROUP BY processdata.taxYear, processdata.empID, company.companyName, company.streetAddress, company.companyPIN,
employeemaster.lastName, employeemaster.firstName, employeemaster.middleName, processdata.edCode
回答by Adriaan Stander
The MySQL equivalent of ISNULLis IFNULL
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
如果 expr1 不为 NULL，则 IFNULL() 返回 expr1；否则返回 expr2。
Maybe also look at SQL NULL Functions
也许也看看SQL NULL 函数
The ISNULL from MySQL is used to check if a value is null
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
如果 expr 为 NULL，则 ISNULL() 返回 1，否则返回 0。
回答by Gordon Linoff
I would suggest that you switch to the ANSI standard function coalesce():
我建议您切换到 ANSI 标准函数coalesce()：
(dbo.OHEM.lastName + ', ' + coalesce(dbo.OHEM.middleName, '') + '' + coalesce(dbo.OHEM.firstName, '')
) AS EmployeeName
You could also make your query easier to read by including table aliases.
您还可以通过包含表别名使您的查询更易于阅读。
EDIT:
编辑：
As a note, I seemed to have missed the direction of conversion. The MySQL query would use concat():
作为说明，我似乎错过了转换的方向。MySQL 查询将使用concat()：
CONCAT(OHEM.lastName, ', ', coalesce(OHEM.middleName, ''),
coalesce(concat(' ', OHEM.firstName), '')
) AS EmployeeName
回答by LConrad
I was getting an error when running JUnit tests against a query which had ISNULL(value)with the error saying ISNULL needed two parameters. I fixed this by changing the query to have value is nulland the code works the same while the tests now work.
我在针对查询运行 JUnit 测试时遇到错误，该查询有ISNULL(value)错误说 ISNULL 需要两个参数。我通过更改查询来解决这个问题，value is null并且代码在测试现在工作时工作相同。
