SQL SERVER – Using Decode in SQL Server

One of the reader of the blog has sent me question regarding how to use DECODE function in SQL Server.

SELECT DECODE(Letters, 'First',1, 'Second',2, 'Third',3, 0) AS LN
 FROM LettersTable

In SQL Server the equivalent code is CASE statement. Here is the examples regarding how DECODE can be written in SQL Server.

SELECT CASE WHEN Letters = 'First' THEN 1
WHEN Letters = 'Second' THEN 2
WHEN Letters = 'Third' THEN 3
ELSE 0 END AS LN
FROM LettersTable

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – Using Decode in SQL Server

  1. A more concise version, following the format of DECODE a little more:

    SELECT CASE Letters
    WHEN ‘First’ THEN 1
    WHEN ‘Second’ THEN 2
    WHEN ‘Third’ THEN 3
    ELSE 0 END AS LN
    FROM LettersTable

    Something else to note is that DECODE is an overloaded function that can take a variable number of parameters. I’m not aware of any functions like this in SQL Server.

  2. OK, dumb question… you’re referring to DECODE from Oracle, right? Might be helpful to add that reference for your non-oracle fluent readers. I was thinking DECODE was a new feature of Denali or something. Thanks Pinal.

  3. CREATE procedure LiabilitySchedules(@EndDate datetime,@Schedule varchar(100))
    as begin

    declare @PrevEndDate datetime

    set @EndDate=@EndDate
    set @PrevEndDate=dateadd(year,-1,@EndDate)

    select distinct T0.[U_EnvSubHd],
    (select U_EnvMisHd from [@ENV_SUB_HEAD] where Code=T0.U_EnvSubHd)[Sub Head],
    (select isnull(sum(T5.[credit]-T5.[Debit]),0.00) FROM OACT T4
    INNER JOIN JDT1 T5 ON T4.AcctCode = T5.Account INNER JOIN OJDT T6 ON T5.TransId = T6.TransId

    and T6.RefDate<=@EndDate and T4.U_EnvSubHd=T0.U_EnvSubHd and T4.U_EnvMainHd=T0.U_EnvMainHd )[Present],

    (select isnull(sum(T5.[credit]-T5.[Debit]),0.00) FROM OACT T4
    INNER JOIN JDT1 T5 ON T4.AcctCode = T5.Account INNER JOIN OJDT T6 ON T5.TransId = T6.TransId and
    T6.RefDate<=@PrevEndDate and T4.U_EnvSubHd=T0.U_EnvSubHd and T4.U_EnvMainHd=T0.U_EnvMainHd)[Previous]
    from [dbo].OACT T0 with(nolock)
    where T0.[U_EnvMainHd]=@Schedule and T0.[U_envAlPha]='Liability'
    end

    Dear Pinal Dave,

    This is my query.
    in this present and previous are the 2 columns which are written in sub query.
    and this query query is taking more than 15 min to execute as the data is very huge.
    please guide in a way to accelerate performance of this query.
    and is there any option instead of sub queries.

  4. Vivek, You want Pinal doing your job ! Good Luck with that…
    By the way:
    1) Analyze the execution plan
    2) Database Engine Tuning Advisor will give you some advice about indexes.
    3) DMVs

  5. i want to use sum() function with “case” in sqlserver, if i m using this, it is asking for the field in “when” clause to be used in “group by” clause – this way, i m not getting correct result
    can i use “case” in “group by “clause

    • Yes you can.
      select [total value] = sum(value)
      , [amount] = case
      when amount = 1 then
      ‘one’
      when amount = 2 then
      ‘two’
      else
      ‘more’
      end
      from orders
      group by case
      when amount = 1 then
      ‘one’
      when amount = 2 then
      ‘two’
      else
      ‘more’
      end

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  7. In fact there is no DECODE in SQL Server as far as I know, but I have achieved the same result in T-SQL but you have to know up front the columns you want to receive, here’s an example.

    Consider you have table of employees with 3 departments : 1=FINANCE, 2=IT and 3=ACCOUNTING,

    SELECT SUM(IT) AS IT,
    SUM(IT) AS IT,
    SUM(FINANCE) AS FINANCE,
    SUM(ACCOUNTING) AS ACCOUNTING,
    FROM (
    SELECT
    CASE WHEN DEPT=1 THEN 1 ELSE 0 END AS FINANCE,
    CASE WHEN DEPT=2 THEN 1 ELSE 0 END AS IT,
    CASE WHEN DEPT=3 THEN 1 ELSE 0 END AS ACCOUNTING,
    FROM EMPLOYEES )
    ;

    Of course this will bring a DECODE for all ROWS, in case you want a group or filter the rows the SQL would be different.

  8. Hi sirji,

    I need a solution for the below query.

    I have a table temp with column a,b,c,d

    A B C D
    1 1 1 m
    1 2 1 d
    1 3 1 w
    2 1 1 m
    2 2 1 d
    2 2 1 w

    SELECT CASE WHEN B=1 AND C=1 THEN D END as T1,
    CASE WHEN B=2 AND C=1 THEN D END as T2,
    CASE WHEN B=3 AND C=1 THEN D END as T3
    FROM TEMP
    WHERE A=1

    MY RESULT SET IS
    T1 T2 T3
    m NULL NULL
    NULL d NULL
    NULL NULL w

    NOW I NEED A RESULT SET WITH SINGLE ROW
    T1 T2 T3
    m d w

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s