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)
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
[...] Using Decode in SQL Server There is no DECODE function in SQL Server, one has to use a CASE statement to simulate this one. [...]
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.