SQL查询需求：按Transaction Type控制Quantity正负值显示

看起来你在CASE语句里犯了个小错误——你写的quantity >=0是个布尔判断，数据库没办法把它当成数值返回，这就是报错的原因。咱们分两种场景来解决你的需求：

场景1：直接过滤掉不符合条件的行 #

如果你想让Delivery和Transfer类型只保留Quantity为正的记录，Used类型保留所有正负记录，那应该把条件加到WHERE子句里，而不是用CASE：

SELECT 
    DISTINCT 
    CASE 
        WHEN NOT UPPER(transaction_type) = 'PURCHASE' AND DST_PROPERTY_TYPE = 'WAREHOUSE' 
        THEN 'Return' 
        ELSE transaction_type 
    END AS transaction_type,
    transaction_type AS transaction_type_OG,
    -- division_name2,
    quantity,
    transaction_id,
    comments,
    src_division_name,
    src_property_name,
    src_property_type,
    src_afe, 
    src_rig_name,
    dst_division_id,
    dst_division_name,
    dst_property_name,
    dst_property_type,
    dst_afe,
    dst_rig_name,
    M.CATEGORY_NAME,
    item_id,
    item_name,
    created_by, 
    ticket_number
FROM transactions.tables m 
WHERE 
    NOT CATEGORY_ID IN ('1294','1295','1337') 
    AND MASTER_PROPERTY NOT LIKE '%products%' 
    AND MASTER_PROPERTY NOT LIKE '%TEST%' 
    AND transaction_dt = TRUNC(SYSDATE) - 1
    -- 核心过滤条件：Used类型全留，Delivery/Transfer只留正Quantity
    AND (
        transaction_type = 'Used' 
        OR (transaction_type IN ('Delivery','Transfer') AND quantity >= 0)
    )
ORDER BY transaction_type, src_property_type, src_property_name;

场景2：保留所有行，但只显示符合条件的Quantity值 #

如果你想保留所有交易行，但Delivery和Transfer类型的负Quantity显示为NULL（或者不显示），Used类型显示原数值，那就要修正CASE语句的写法：

SELECT 
    DISTINCT 
    CASE 
        WHEN NOT UPPER(transaction_type) = 'PURCHASE' AND DST_PROPERTY_TYPE = 'WAREHOUSE' 
        THEN 'Return' 
        ELSE transaction_type 
    END AS transaction_type,
    transaction_type AS transaction_type_OG,
    -- division_name2,
    -- 修正后的CASE：先判断交易类型，再对Delivery/Transfer的Quantity做正负校验
    CASE 
        WHEN transaction_type IN ('Delivery','Transfer') THEN 
            CASE WHEN quantity >= 0 THEN quantity ELSE NULL END
        ELSE quantity
    END AS quantity,
    transaction_id,
    comments,
    src_division_name,
    src_property_name,
    src_property_type,
    src_afe, 
    src_rig_name,
    dst_division_id,
    dst_division_name,
    dst_property_name,
    dst_property_type,
    dst_afe,
    dst_rig_name,
    M.CATEGORY_NAME,
    item_id,
    item_name,
    -- 可选：保留原始Quantity字段用于对比
    quantity AS original_quantity,
    created_by, 
    ticket_number
FROM transactions.tables m 
WHERE 
    NOT CATEGORY_ID IN ('1294','1295','1337') 
    AND MASTER_PROPERTY NOT LIKE '%products%' 
    AND MASTER_PROPERTY NOT LIKE '%TEST%' 
    AND transaction_dt = TRUNC(SYSDATE) - 1
ORDER BY transaction_type, src_property_type, src_property_name;

关键错误解释 #

你原来的CASE语句case when transaction_type in ('Delivery','Transfer') then quantity >=0 else quantity end会报错，因为quantity >=0返回的是布尔值（TRUE/FALSE），而你期望返回的是数值类型的Quantity。数据库无法在同一个CASE分支里混合返回布尔值和数值，所以必须改成先判断Quantity是否为正，再返回对应的数值或者NULL。

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