Equivalent of 'DECODE' in sql

  • Hi all,

    🙂 Can anyone provide me with correct syntax for' 'DECODE' function equivalent in sql.

    Thanks,

    kiran.

    Kiran

  • Use

    CASE ... WHEN ... THEN ... ELSE ... END

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • i have a table called university which have two fields id and name.

    i tried this way.

    SELECT CASE u.id

    WHEN 2 THEN 'GOOD'

    WHEN 3 THEN 'POOR'

    WHEN 4 THEN 'EXCELLENT'

    ELSE 'BAD'

    END CASE

    from university u;

    it is not executing.can u suggest me where my query is wrong.....

    Kiran

  • Should be something like this (http://msdn2.microsoft.com/en-us/library/aa258235(SQL.80).aspx) ?

    USE pubs

    GO

    SELECT Category =

    CASE type

    WHEN 'popular_comp' THEN 'Popular Computing'

    WHEN 'mod_cook' THEN 'Modern Cooking'

    WHEN 'business' THEN 'Business'

    WHEN 'psychology' THEN 'Psychology'

    WHEN 'trad_cook' THEN 'Traditional Cooking'

    ELSE 'Not yet categorized'

    END,

    CAST(title AS varchar(25)) AS 'Shortened Title',

    price AS Price

    FROM titles

    WHERE price IS NOT NULL

    ORDER BY type, price

    COMPUTE AVG(price) BY type

    GO

  • Kiran,

    You were very close to getting your code to run. The line "END CASE" should just be "END". I wrote a test case demonstration, based on your information.

    -- Create university Table.

    CREATE TABLE dbo.university

    (

    [id] NUMERIC(10,0)

    ); NUMBER(10,0)

    );

    -- Insert sample data.

    INSERT INTO university VALUES (1);

    INSERT INTO university VALUES (2);

    INSERT INTO university VALUES (3);

    INSERT INTO university VALUES (4);

    INSERT INTO university VALUES (5);

    -- Select the data as a preview.

    select [id] from university

    -- Build the case statement with the following Rules:

    -- ID of 2 = GOOD

    -- ID of 3 = POOR

    -- ID of 4 = EXCELLENT

    -- all other ID values default to = BAD

    SELECT

    [id], -- Display the original value for [id].

    CASE u.[id]

    WHEN 2 THEN 'GOOD'

    WHEN 3 THEN 'POOR'

    WHEN 4 THEN 'EXCELLENT'

    ELSE 'BAD'

    END AS 'GRADE_REMARK' -- this is just a column label

    FROM dbo.university AS u

    -- Clean up the demonstration.

    -- Drop the university table.

    DROP TABLE dbo.university

    Let me know if this is what you were expecting.

    Best Regards,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • Thanks Wilson,

    It's working :).

    Kiran

  • Kiran,

    Thanks for posting that everything is working. See you around the Forums.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • hi,

    The decode functionality in oracle is something similar to

    "if then else...". But what you have stated is "select case...end"

    let me clearly explain you a scinario,

    table student contains name and total marks

    i can say "pass" or "fail" by give a query like

    "select name, decode(total > 250, "pass","fail") status from student"

    how will u achieve the same scinario in sql-server.

    regds

    rishi

  • Rishi,

    You can do the same with 'select case...end'

    SELECT

    CASE

    WHEN total > 250 THEN 'pass'

    ELSE 'fail'

    END as 'status'

    FROM student

    -Vetri

  • Dear vetri,

    What you have provided was sounds fine..but actually im looking for any in-built function like how oracle provided the "DECODE"........

  • Just for the record (I know this is an old thread).

    You can't do this in Oracle:

    "select name, decode(total > 250, "pass","fail") status from student"

    The syntax for the decode function is:

    decode( expression , search , result [, search , result]... [, default] )

    expression is the value to compare.

    search is the value that is compared against expression.

    result is the value returned, if expression is equal to search.

    default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

    So, you can't use > with the decode function. It just evaluates if expression matches (i.e. is equal to) search

  • 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

  • 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

  • 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

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • 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

    Thank a lot Jonathan..!!!!

Viewing 15 posts - 1 through 15 (of 15 total)

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