Postgresql更新查询(带有子查询)错误

Question

我在查询时遇到错误。第一个内部查询在我们独立运行时正确执行。但是当我在完整的查询中包含相同的内容时，它会提示错误。

UPDATE GL_BudgetPlanDetails bpd 
SET (CommittedAmt07) = (
        SELECT SUM (case
            when fa.amtacctdr > 0 then (fa.amtacctdr*-1)
            when fa.amtacctcr > 0 then fa.amtacctcr else 0 end )
        FROM fact_acct fa
        WHERE fa.datetrx between '2014-09-01' and '2014-09-30'
          AND fa.isactive ='Y'
          AND fa.account_ID =758
          AND fa.AD_Client_ID =11
          AND fa.AD_Org_ID =50001)
WHERE (
        (
            SELECT gbp.c_year_id
              FROM GL_BudgetPlan gbp
             WHERE gbp.GL_BudgetPlan_ID = bpd.GL_BudgetPlan_ID
               AND gbp.isactive ='Y'
        ) = '2014'
      )
AND bpd.account_ID =758

Answer 1

去掉 CommittedAmt07 的括号

UPDATE GL_BudgetPlanDetails bpd SET Period01Amt=(SELECT SUM(当 fa.amtacctdr > 0 然后 (fa.amtacctdr*-1) 当 fa.amtacctcr > 0然后 fa.amtacctcr else 0 end ) FROM fact_acct fa WHERE fa.datetrx between '2014-09-01' and '2014-09-30' AND fa.isactive ='Y' AND fa.account_ID =758AND fa.AD_Client_ID =11 AND fa.AD_Org_ID =50001) WHERE ((SELECT gbp.c_year_id从 GL_BudgetPlan gbp WHERE gbp.GL_BudgetPlan_ID = bpd.GL_BudgetPlan_ID AND gbp.isactive ='Y' ) = '2014') AND bpd.account_ID =758