DOC

Oracle_Function_DECODE

By Beth Price,2014-07-20 14:39
11 views 0
Oracle_Function_DECODE

    Title : SQL Reference

    Topic : Function

    Subject : DECODE() & SIGN()

    DECODE() 的作用在於解碼;它可以將特定的資料轉換成另一指定的資料;語

    法如下?

    DECODE( expr, [search1, result1,] [search2, result2,] default )

    Oracle 會先解析 expr;然後逐一的與search 條件比對;如果發現 expr 相等於

    search 條件;變傳回對應的 result;如果完全沒有發現;就傳回 default;若無指

    定預設值;就傳回 NULL。舉例如下

SELECT lot_status, DECODE(lot_status, 'WAIT', 'Lot is waiting for dispatch',

     'DISP', 'Lot is dispatched',

     'RUN', 'Lot is Running',

     'HELD', 'Lot is Held',

     'Other') lot_status_desc, sum(1) lot_qty

     FROM R_LOT_LOTX

    GROUP BY lot_status

    Lot Status Lot Status Desc Lot Qty

    HELD Lot is Held 2

    RUN Lot is Running 9

    WAIT Lot is waiting for dispatch 290

    SOUR Other 1

    TERM Other 566

    以上是一般的使用方法;另外我們可以用些技巧將上述的資料轉向

     SELECT SUM(DECODE(lot_status, 'WAIT', 1, 0 )) "Lot is waiting for dispatch",

     SUM(DECODE(lot_status, 'DISP', 1, 0 )) "Lot is dispatched",

     SUM(DECODE(lot_status, 'RUN', 1, 0 )) "Lot is Running",

     SUM(DECODE(lot_status, 'HELD', 1, 0 )) "Lot is Held",

     SUM(DECODE(lot_status, 'WAIT', 0, 'DISP', 0, 'RUN', 0, 'HELD', 0, 1)) "Lot

     Status Desc"

     FROM R_LOT_LOTX

    Lot is waiting for dispatch Lot is dispatched Lot is Running Lot is Held Others

    290 0 9 2 1042

    DECODE() 只能比對相同與否;如果要比大小;可以加上 SIGN();SIGN( expr )會判斷 expr 的值大於 0 與否;若大於 0;回傳 1?等於 0;回傳 0?小於 0;回傳 -1。如下例

SELECT TO_CHAR(sysdate, 'YYYY.MM.DD_HH24:MI:SS') "Now",

     TO_CHAR(CAST(hold_date AS DATE), 'YYYY.MM.DD_HH24:MI:SS') "Hold Date",

     ROUND(sysdate - CAST(hold_date AS DATE)) "Hold Days",

     DECODE(SIGN(ROUND(sysdate - CAST(hold_date AS DATE))-20), -1, 'It is OK!',

    0, 'Be Careful', 1, 'Ouch!') "Status"

     FROM R_LOT_LTHR

    Now Hold Date Hold Days Status

    2004.04.01_12:28:08 2004.03.09_18:42:12 23 Ouch!

    2004.04.01_12:28:08 2004.03.09_18:53:13 23 Ouch!

    2004.04.01_12:28:08 2004.03.12_15:00:17 20 Be Careful

    2004.04.01_12:28:08 2004.03.12_15:00:18 20 Be Careful

    2004.04.01_12:28:08 2004.03.22_18:48:33 10 It is OK!

    最後再來個宇宙超級霹靂無敵組合技吧

SELECT DECODE(GROUPING(hold_code), 1, 'Total', hold_code) "Hold Code",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-10), -1, 1, 0, 1, 0)) "In 10 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-20), -1, DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-10), 1, 1, 0,

    1, 0)

     , 0, 1, 0)) "Between 10 and 20 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-20), -1, 1, 0, 1, 0)) "In 20 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-30), -1, DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-20), 1, 1, 0,

    1, 0)

     , 0, 1, 0)) "Between 20 and 30 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-30), -1, 1, 0, 1, 0)) "In 30 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-40), -1, DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-30), 1, 1, 0,

    1, 0)

     , 0, 1, 0)) "Between 30 and 40 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-40), -1, 1, 0, 1, 0)) "In 40 Days",

     SUM(DECODE(SIGN(sysdate - CAST(hold_date AS DATE)-40), -1, 0, 0, 1, 1)) "More then 40 Days",

     SUM(1) "Total"

     FROM R_LOT_LTHR

    GROUP BY ROLLUP (hold_code)

    Between Between Between In 10 In 20 In 30 In 40 More then Hold Code 10 and 20 20 and 30 30 and 40 Total Days Days Days Days 40 Days Days Days Days

    FUTURE_MERGE 0 0 0 0 0 100 100 66 166 OC 0 1 1 8 9 0 9 0 9 OS 1 1 2 12 14 0 14 0 14 OT 0 0 0 0 0 0 0 2 2 Total 1 2 3 20 23 100 123 68 191

Report this document

For any questions or suggestions please email
cust-service@docsford.com