如何在PostgreSQL中将十进制转换为isnull时修复语法错误

Question

我正在将MS SQL Server存储过程转换为PostgresSQL中的存储函数。需要使用合并将十进制值转换为 isnull，但在那行中我遇到了语法错误。

存储功能：

CREATE OR REPLACE FUNCTION GetDeptListForViewModifyJointUsePercentages (
     p_nInstID numeric,
     p_nDeptID numeric) RETURNS VOID
AS  $$  
declare v_AccountTotal decimal(18,2);
BEGIN
select SUM(CONVERT(decimal(18,2), coalesce(eam.npayroll_amt, 0))) as AccountTotal
        FROM 
            Account acct
            INNER JOIN employeeaccountmapping eam 
                ON eam.nacct_id = acct.naccount_id
                AND acct.ninst_id =p_nInstID
          where acct.ndept_id =p_nDeptID
)

输出窗格：

    ERROR:  syntax error at or near ","
LINE 7: select SUM(CONVERT(decimal(18,2), coalesce(eam.npayroll_amt,...
                                        ^

Answer 1

你可能想要一个类型转换：

CAST (coalesce(...) AS decimal(18,2))