如何在SQL CASE语句中整合DECODE，满足条件时显示指定金额

你的SQL写法存在两个关键语法/逻辑错误：

1. CASE语句的WHEN子句后不能直接给条件添加别名a，SQL语法不支持这种写法；
2. DECODE函数的参数顺序错误，DECODE的标准格式是DECODE(判断表达式, 匹配值1, 返回结果1, 匹配值2, 返回结果2, 默认结果)，你写的decode(a, rt.amount, 0)逻辑完全颠倒。

以下是两种符合需求的实现方式：

方式1：分离标识列与金额列（推荐） #

同时保留thrd_prty_domestic（1/0标识）和对应的金额列，逻辑清晰易维护：

-- 1. 判断是否符合第三方国内条件的标识列
CASE 
    WHEN state_expense_purpose_desc = 1 
         AND (travel_country = sr.country_id OR travel_country IS NULL)
    THEN '1' 
    ELSE '0' 
END AS thrd_prty_domestic,
-- 2. 符合条件时显示金额，否则显示0（可根据需求改为NULL）
CASE 
    WHEN state_expense_purpose_desc = 1 
         AND (travel_country = sr.country_id OR travel_country IS NULL)
    THEN rt.amount 
    ELSE 0 
END AS thrd_prty_domestic_amount

方式2：用DECODE关联标识与金额（适用于Oracle等支持DECODE的数据库） #

如果需要通过DECODE直接关联标识判断和金额返回，可以这样写：

-- 先定义标识列
CASE 
    WHEN state_expense_purpose_desc = 1 
         AND (travel_country = sr.country_id OR travel_country IS NULL)
    THEN '1' 
    ELSE '0' 
END AS thrd_prty_domestic,
-- 基于标识列的金额返回（用DECODE实现）
DECODE(
    CASE 
        WHEN state_expense_purpose_desc = 1 
             AND (travel_country = sr.country_id OR travel_country IS NULL)
        THEN '1' 
        ELSE '0' 
    END,
    '1', rt.amount,
    0
) AS thrd_prty_domestic_amount

说明：原条件(travel_country = sr.country_id AND state_expense_purpose_desc = 1) OR (travel_country IS NULL AND state_expense_purpose_desc = 1)可以简化为state_expense_purpose_desc = 1 AND (travel_country = sr.country_id OR travel_country IS NULL)，逻辑等价但更简洁。

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