Oracle SQL中CASE语句别名筛选触发ORA-00904报错的解决咨询

在Oracle SQL里，WHERE子句的执行顺序早于SELECT子句——也就是说当数据库处理WHERE条件的时候，SELECT里定义的别名STATUS_TEST还未被创建，所以不管你给别名加不加AS或者引号，都会触发ORA-00904错误。

没法直接在WHERE里用SELECT定义的CASE别名，不过有几种替代方案可以实现需求：

方案1：使用子查询 #

把包含CASE逻辑的查询作为子查询，在外层WHERE里筛选别名：

SELECT *
FROM (
    SELECT transfer_order_number, item_number, item_description, requested_qty, received_qty, shipped_qty, 
            CASE 
        WHEN shipped_qty = requested_qty THEN 'Shipped'
        WHEN shipped_qty < requested_qty THEN 'Partially Shipped'
        WHEN shipped_qty = requested_qty AND received_qty < requested_qty THEN 'Shipped and Partially Received'
        WHEN shipped_qty < requested_qty AND received_qty < requested_qty THEN 'Partially Shipped and Partially Received'
        WHEN shipped_qty = requested_qty AND received_qty = requested_qty THEN 'Shipped and Received'
        WHEN shipped_qty IS NULL THEN 'Awaiting Fulfillment'
        END STATUS_TEST
    FROM TransferOrder_TABLE
) t
WHERE t.STATUS_TEST = 'Partially Shipped and Partially Received'

方案2：使用CTE（公共表表达式） #

用WITH子句先构建包含状态字段的临时结果集，再筛选：

WITH transfer_status AS (
    SELECT transfer_order_number, item_number, item_description, requested_qty, received_qty, shipped_qty, 
            CASE 
        WHEN shipped_qty = requested_qty THEN 'Shipped'
        WHEN shipped_qty < requested_qty THEN 'Partially Shipped'
        WHEN shipped_qty = requested_qty AND received_qty < requested_qty THEN 'Shipped and Partially Received'
        WHEN shipped_qty < requested_qty AND received_qty < requested_qty THEN 'Partially Shipped and Partially Received'
        WHEN shipped_qty = requested_qty AND received_qty = requested_qty THEN 'Shipped and Received'
        WHEN shipped_qty IS NULL THEN 'Awaiting Fulfillment'
        END STATUS_TEST
    FROM TransferOrder_TABLE
)
SELECT *
FROM transfer_status
WHERE STATUS_TEST = 'Partially Shipped and Partially Received'

方案3：在WHERE子句中重复CASE逻辑 #

这种方法不需要嵌套查询，但会让代码冗余，适合简单的CASE逻辑：

SELECT transfer_order_number, item_number, item_description, requested_qty, received_qty, shipped_qty, 
        CASE 
    WHEN shipped_qty = requested_qty THEN 'Shipped'
    WHEN shipped_qty < requested_qty THEN 'Partially Shipped'
    WHEN shipped_qty = requested_qty AND received_qty < requested_qty THEN 'Shipped and Partially Received'
    WHEN shipped_qty < requested_qty AND received_qty < requested_qty THEN 'Partially Shipped and Partially Received'
    WHEN shipped_qty = requested_qty AND received_qty = requested_qty THEN 'Shipped and Received'
    WHEN shipped_qty IS NULL THEN 'Awaiting Fulfillment'
    END STATUS_TEST
FROM TransferOrder_TABLE
WHERE CASE 
    WHEN shipped_qty = requested_qty THEN 'Shipped'
    WHEN shipped_qty < requested_qty THEN 'Partially Shipped'
    WHEN shipped_qty = requested_qty AND received_qty < requested_qty THEN 'Shipped and Partially Received'
    WHEN shipped_qty < requested_qty AND received_qty < requested_qty THEN 'Partially Shipped and Partially Received'
    WHEN shipped_qty = requested_qty AND received_qty = requested_qty THEN 'Shipped and Received'
    WHEN shipped_qty IS NULL THEN 'Awaiting Fulfillment'
    END = 'Partially Shipped and Partially Received'

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