DECODE與CASE WHEN區別

兩者很像，都是if else
※DECODE

SELECT product_id,
DECODE (warehouse_id, 
        1, 'Southlake',
        2, 'San Francisco',
        3, 'New Jersey',
        4, 'Seattle',
        'Non domestic') "Location"
FROM inventories;

這是官方的範例，如果warehouse_id欄位是1就顯示Southlake，以此類推，如果都不是就顯示Non domestic

※CASE WHEN
換成CASE WHEN的寫法

SELECT product_id,
(
    CASE product_id
    WHEN 1 THEN 'Southlake'
    WHEN 2 THEN 'San Francisco'
    WHEN 3 THEN 'New Jersey'
    WHEN 4 THEN 'Seattle'
    ELSE 'Non domestic'
    END
) "Location"
FROM inventories;

※但如果有兩個以上要顯示一樣，CASE WHEN 會比較方便，因為它有第二種寫法，CASE後面不接欄位，欄位變成在WHEN後面

SELECT product_id,
(
    CASE
    WHEN product_id IN(1, 2, 4) THEN 'Southlake'
    WHEN product_id = 3 THEN 'New Jersey'
    ELSE 'Non domestic'
    END
) "Location"
FROM inventories;

如果用DECODE就會出現重復的值，它沒辦法有條件，會出錯

DECODE (warehouse_id, 
        1, 'Southlake',
        2, 'Southlake',
        3, 'New Jersey',
        4, 'Southlake',
        'Non domestic') "Location"
FROM inventories;

※判斷不同欄位

SELECT product_id,
(
    CASE 
    WHEN product_id=1 THEN 'Southlake'
    WHEN product_name=2 THEN 'San Francisco'
    WHEN product_code=3 THEN 'New Jersey'
    WHEN product_title=4 THEN 'Seattle'
    ELSE 'Non domestic'
    END
) "Location"
FROM inventories;

※如果要判斷的欄位都一樣，就將欄位放在case後面會比較方便，如果不一樣就要用這種寫法

※注意ORA-00932: 不一致的資料類型: 應該是 xxx型態, 但為 xxx型態

SELECT
(
    CASE WHEN  1=2
           THEN '1'
           ELSE 2
    END
) TEST
FROM dual;

這樣會報錯，要把整個CASE視為一個整體，THEN和ELSE回傳的型態要統一就不會報這個錯了