如何在普通SELECT查询中相加跨表DECIMAL列值及报错修复

问题重现 #

在普通SELECT查询中，尝试将LOAN和LOAN_PARTY表的DECIMAL类型列值相加时，触发语法错误：‘+’和‘)’附近语法不正确，错误出现在CASE语句的ELSE分支计算逻辑中。

根因分析 #

排查后确认：ELSE分支的合并计算语句遗漏了SELECT关键字，导致SQL解析器无法正确识别子查询结构，进而报错。

修正后的SQL示例 #

SELECT 
    base.loan_id,
    CASE
        -- 关联到LOAN_PARTY记录时，两表DECIMAL列相加
        WHEN party.party_id IS NOT NULL THEN
            (SELECT l.principal_amount + lp.contribution_amount 
             FROM LOAN l 
             JOIN LOAN_PARTY lp ON l.loan_id = lp.loan_id 
             WHERE l.loan_id = base.loan_id)
        -- 无关联记录时，仅取LOAN表的列值（此处需添加SELECT关键字）
        ELSE
            (SELECT l.principal_amount 
             FROM LOAN l 
             WHERE l.loan_id = base.loan_id)
    END AS total_calculated_amount
FROM LOAN base
LEFT JOIN LOAN_PARTY party ON base.loan_id = party.loan_id;

内容的提问来源于stack exchange，提问作者mmar