One of the readers of the blog has sent me a question regarding how to use the 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 are 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
Here are few alternative blog posts.
SQL SERVER – Alternate to AGENT_DATETIME Function
Sometimes back I posted about interesting function AGENT_DATETIME functions which will convert integers into datetime values. Someone commented that there might be some permission issues using this function which is only available in msdb database. So I write this post to give you alternate method without using any such functions.
SQL SERVER – What is T-SQL Window Function Framing? – Notes from the Field #102
One of the best kept secrets of T-SQL window functions, also called windowing or windowed functions, is framing. Framing was introduced with SQL Server 2012, and it allows the set of rows, or window, to be defined very granularly. For example, when calculating a running total, each row needs to see an increasing number of values to perform the calculation. Row 1 needs to see just row 1. Row 2 needs to see rows 1 and 2. Row 3 needs to see rows 1, 2, and 3. Framing allows each row to have a distinct set of rows for the window function calculation.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
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.
Nick,
COALESCE is an example of a T-SQL function that can take a variable number of parameters.
Would be great to see a list of all the Sql functions etc that allow variable usage..
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.
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.
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
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
case what is case exactly when i execute my query it says “Incorrect syntax near the keyword ‘CASE’.”
If Possible add this case
when amount = 1 OR amount = 1 then
‘one’
then
‘two’
else
‘more’
end
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.
sir,
What is the use of STUFF and XML PATH in SQL SERVER..
Please Explain With Examples
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
select
MAX( T1) T1,MAX(T2) T2,MAX(T3)T3 FROM
(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)TE
Hi .. can anyone help in modiying the below decode statement to run using CASE in sql server
Select f.fge_code_id, f.fge_libelle as incident,
decode(f.fge_code_id,’236′,’920′,’243′,’720′,’218′,’910′,’220′,’910′,’247′,’720′,’251′,’799′,’215′,’610′,’185′,’899′,’255′,’799′,’221′,’930′,’266′,’440′,’183′,
‘410’,’265′,’410′,’217′,’910′,198,499,187,440,’183′,’410′,
186,460,637,899,263,910,184,499,261,499,’257′,’899′,’194′,’499′,190, 460,633,710,’241′,’1199′,’213′,’610′,’232′,’1130′,203,499,632,110,924,899,212,610,250,730,
189,499,267,450,235,920,242,999,1430,1320,192,610,200,499,’1′) as plugsellcode
From contrats.fgs f
order by fge_code_id
Excelente, muchas gracias por el tip.