Equivalent of 'DECODE' in sql

  • rachit.agarwal2011

    SSC Rookie

    Points: 35

    drew.allen - Tuesday, January 29, 2019 10:23 AM

    Jonathan AC Roberts - Tuesday, January 29, 2019 5:15 AM

    rachit.agarwal2011 - Tuesday, January 29, 2019 4:40 AM

    decode(e.V_Billing_Currency_Code,a.V_CHECK_CURRENCY_CODE,d.N_Applied_Amount,decode(a.N_Exchange_Rate,0,0,(d.N_Domain_Amount/ a.N_Exchange_Rate)))

    Could anyone help me in converting this to CASE statement

    I think this is it:
    CASE e.V_Billing_Currency_Code
        WHEN a.V_CHECK_CURRENCY_CODE THEN d.N_Applied_Amount
        ELSE CASE a.N_Exchange_Rate
                  WHEN 0 THEN 0
                  ELSE (d.N_Domain_Amount/ a.N_Exchange_Rate)
             END
    END

    This can be simplified to CASE
        WHEN e.V_Billing_Currency_Code = a.V_CHECK_CURRENCY_CODE THEN d.N_Applied_Amount
        WHEN a.N_Exchange_Rate = 0 THEN 0
        ELSE (d.N_Domain_Amount/ a.N_Exchange_Rate)
    END

    Part of the art of translating is knowing when to deviate from a literal translation.  Since CASE has two alternate syntaxes, it's tempting to use the syntax that more closely resembles the DECODE syntax, but the other syntax gives a simpler formula.

    Drew

    Thank you Drew...
    It really helped me a lot..

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply