mysql - 调用 native 函数时参数计数不正确 'ISNULL'

Question

我有一个查询，我正在尝试从 MS SQL Server 2008 转换为 MySQL。它在 MSSQL 上运行良好，我得到错误“对 native 函数 'ISNULL' 的调用中的参数计数不正确”。

我该如何解决这个问题？

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

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

Answer 1

MySQL 相当于 ISNULL是IFNULL

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

也许还看看SQL NULL Functions

The ISNULL from MySQL is used to check if a value is null

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.