Equivalent of 'DECODE' in sql

• rachit.agarwal2011

  SSC Rookie

  Points: 35

  More actions

  January 30, 2019 at 12:19 am

  #2020527

  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..