Oracle DECODE 函數多條件排序改用 CASE WHEN 實作

問題現象

需求：依 de.status 排序，但當 status=2 且 結束時間 - 開始時間 > 0 時，需調整排序優先級。DECODE 單一值比較，無法直接處理複合條件。

DECODE 限制

-- DECODE 只能做單值等值比較
ORDER BY DECODE(de.status, 2, 1, 3, 2, 1, 3, 4, 4)
-- 無法加入：AND (de.end_time - de.start_time) > 0

解決方案：CASE WHEN 多條件排序

ORDER BY 
    CASE 
        WHEN de.status = 2 AND (de.end_time - de.start_time) > 0 THEN 1  -- 最高優先
        WHEN de.status = 2 THEN 2
        WHEN de.status = 3 THEN 3
        WHEN de.status = 1 THEN 4
        WHEN de.status = 4 THEN 5
        ELSE 99
    END,
    de.start_time  -- 次要排序：開始時間

完整查詢範例

SELECT de.*
FROM delivery de
ORDER BY 
    CASE 
        WHEN de.status = 2 AND (de.end_time - de.start_time) > 0 THEN 1
        WHEN de.status = 2 THEN 2
        WHEN de.status = 3 THEN 3
        WHEN de.status = 1 THEN 4
        WHEN de.status = 4 THEN 5
        ELSE 99
    END,
    de.created_at DESC;

CASE WHEN vs DECODE 對比

進階：動態排序欄位

ORDER BY 
    CASE :sort_by
        WHEN 'status_priority' THEN 
            CASE 
                WHEN de.status = 2 AND (de.end_time - de.start_time) > 0 THEN 1
                WHEN de.status = 2 THEN 2
                ELSE 99
            END
        WHEN 'created_desc' THEN de.created_at
        WHEN 'amount_asc' THEN de.amount
        ELSE de.id
    END DESC;

常見錯誤