CASE expresses the DECODE mapping in this SQL Server example. I keep the named matches and default value explicit.
A reader sent an Oracle-style DECODE expression mapping First, Second, and Third to 1, 2, and 3. Its fallback is 0. SQL Server’s CASE expression represents that particular mapping.
DECLARE @Letters TABLE (Letters varchar(10));
INSERT INTO @Letters VALUES ('First'),('Second'),('Third'),('Other'),(NULL);
SELECT Letters,
CASE WHEN Letters = 'First' THEN 1
WHEN Letters = 'Second' THEN 2
WHEN Letters = 'Third' THEN 3
ELSE 0 END AS LN
FROM @Letters;
For these inputs, the named values map as requested. Other and NULL reach ELSE 0. The query does not request a presentation order, so interpret the values by their Letters column.
Review differences in a broader conversion
Don’t call CASE a byte-for-byte replacement for every Oracle DECODE expression. NULL comparison, implicit conversion, collation, and return types deserve review. A searched CASE branch using IS NULL can express an intended NULL match.
The related datetime-conversion and window-framing resources remain below. They are additional reading, not prerequisites for the small mapping.
Related reading
- SQL SERVER – Alternate to AGENT_DATETIME Function
- convert integers into datetime values
- SQL SERVER – What is T-SQL Window Function Framing? – Notes from the Field #102
A conversion between expressions is not automatic equivalence, it is a mapping whose types and NULL rules need review.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.