Equivalent of 'DECODE' in sql

  • kiran-670359

    SSC Eights!

    Points: 853

    Hi all,

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

    Thanks,

    kiran.

    Kiran

  • Mark Cowne

    One Orange Chip

    Points: 26694

    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
  • kiran-670359

    SSC Eights!

    Points: 853

    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

  • gan-685682

    Ten Centuries

    Points: 1136

    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

  • Key DBA

    SSCertifiable

    Points: 6029

    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

  • kiran-670359

    SSC Eights!

    Points: 853

    Thanks Wilson,

    It's working :).

    Kiran

  • Key DBA

    SSCertifiable

    Points: 6029

    Kiran,

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

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

  • msraghunathan

    SSC Veteran

    Points: 229

    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

  • vetrivels

    SSC Enthusiast

    Points: 101

    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

  • msraghunathan

    SSC Veteran

    Points: 229

    Dear vetri,

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

  • cadlg

    SSC Journeyman

    Points: 75

    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

  • rachit.agarwal2011

    SSC Rookie

    Points: 35

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 16999

    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

  • drew.allen

    SSC Guru

    Points: 76658

    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

  • rachit.agarwal2011

    SSC Rookie

    Points: 35

    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 16 total)

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