CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.
Two basic formulations for CASE expression
1) Simple CASE expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Syntax:
CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
2) Searched CASE expressions
A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:
Syntax:
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:
DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END
Reference : Pinal Dave (https://blog.sqlauthority.com)
149 Comments. Leave new
Could you guys help with this one?
In the case statement below i an getting only data for the first two case statement, and the last 3 statements are suppressed.
But when i put the second case statement in the end(i.e ‘Total FEW’) i am getting value for the first four condition and only the ‘Total FEW’ condition is suppressed.
Pls give a solution!
Case
When T2.A = ‘CANADA’ and T2.B in (‘TRANSACTION FRAUD’, ‘IDENTITY FRAUD’, ‘INVESTIGATIONS’) Then ‘Canada’
When T2.B ‘OTHER’ Then ‘Total FEW’
When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CANADA’, ‘CHOICE’, ‘DEBIT’, ‘DEBIT EAST’, ‘DEBIT WEST’, ‘OTHER’) and T2.B in (‘SMITH BARNEY’, ‘SPECIALTY’, ‘BOARDROOM’, ‘DOSS’) Then ‘DOSS’
When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CHOICE’, ‘CANADA’, ‘OTHER’) and T2.B = ‘IDENTITY FRAUD’ Then ‘ID Fraud’
When T2.A in (‘BANK CARD EAST’, ‘BANK CARD WEST’, ‘CHOICE’, ‘OTHER’) and T2.B = ‘TRANSACTION FRAUD’ Then ‘Trans Fraud’
End Department,
You need to post some sample data with expected result
Using MSSQL 2008 Adventureworks.
The following syntax is wrong, but is something I want to do.
i.e.
If it is Friday today, select out all the data with Currency_Code IN (‘AED’, ‘AFA’, ‘ALL’)
Else, select out Currency_Code IN (‘AED’, ‘AFA’)
SELECT * FROM [AdventureWorks].[Sales].[Currency] a
WHERE a.Currency_Code IN
CASE DATEPART(dw, GetDate())
WHEN 6 THEN ‘AED’, ‘AFA’, ‘ALL’ — Friday
ELSE ‘AED’, ‘AFA’
END
Please with the SQL statement. Thanks
Correction:
Change a.Currency_Code to a.CurrencyCode
Just to provide more info, this is what I want to achieve:
DECLARE @var VARCHAR(1000)
SELECT @var = CASE DATEPART(dw, GetDate())
WHEN 6 THEN ”’AED”,”AFA”,”ALL”’
ELSE ”’AED”,”AFA”’
END
PRINT @var
— 1st SQL : does not return any result ???
SELECT * FROM [AdventureWorks].[Sales].[Currency] a
WHERE a.CurrencyCode IN (@var)
SELECT @var = ‘SELECT * FROM [AdventureWorks].[Sales].[Currency] a
WHERE a.CurrencyCode IN (‘+@var+’)’
PRINT @var
— 2nd SQL : This is the result I want if it is Friday.
EXEC (@var)
Since today is Friday, it will return as per the 2nd SQL:
CurrencyCode Name ModifiedDate
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
ALL Lek 1998-06-01 00:00:00.000
If today is not Friday, I’m expecting this result:
CurrencyCode Name ModifiedDate
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
Thanks again
Hi,
I got three separate T-SQL scripts to run. Example
search emp table for code
select code,fname,lname from emp where fname =@lookfor
if code = ‘A’
select * from emp
This is just an example i got more line sof tsql code to run if code =’a’.
if code = ‘b’
select @ from dept
if code = ‘c’
select * from add
Hello everyone! This message could not be written better! Reading this post reminds me of my roommate before! He always kept talking about it. I will send this article to him. Pretty sure he will enjoy reading. Thank you for sharing!
Hi All Experts.
I came up with some problem that I am trying to solve, but no luck. Any help will be appreciated.
Claim_id cycle First_date first_rank Second_date second rank 300 1 2011-02-25 0-13 2011-03-10 14-27 300 2 2010-09-15 14-27 2010-10-01 0-13 301 1 2011-04-14 14-27 2011-06-20 14-27 301 2 2010-08-04 14-27 2010-08-20 0-13 403 1 2011-02-09 14-27 2011-02-24 0-13 403 2 2010-08-13 28+ NULL NULL 404 1 2011-06-16 14-27 NULL NULL 404 2 2011-02-25 0-13 2011-03-08 14-27 404 3 2010-10-21 28+ 2010-11-16 14-27 405 1 2011-06-11 14-27 NULL NULL 405 2 2011-03-31 28+ NULL NULL 405 3 2010-08-10 28+ 2010-09-20 14-27 Following conditions are required:
when first rank is 0-13 than records for cycle 1 (no matter what the second rank is) for that claim_id
when first_rank is other than ‘0-13’ and second_rank is not null than records of cycle 1 is needed for that claimid
when first_rank is other than ‘0-13’ and second_rank is null than next cycle record is needed for that claimid till second_rank is not null .
Below is the required result:
Claim_id cycle First_date first_rank Second_date second rank 300 1 2011-02-25 0-13 2011-03-10 14-27 301 1 2011-04-14 14-27 2011-06-20 14-27 403 1 2011-02-09 14-27 2011-02-24 0-13 404 2 2011-02-25 0-13 2011-03-08 14-27 405 3 2010-08-10 28+ 2010-09-20 14-27
In table module is Integer datatype.
select
case @module
when ‘None’ then 0
when ‘Students’ then 1
when ‘EMedExam’ then 2
when ‘ARAP’ then 3
when ‘OutReach’ then 4
when ‘Referrals’ then 5
when ‘EmpPortal’ then 6 end
from SCTASKTABLE
Im facing error conversion failed varchar to int
please help me can any one send solution to my emailid
[email removed]
Thanks in Advance
What is the datatype of @module?
I want to retrieve the result based on the filter values if i pass o then i shud get all join result or if i pass filtered value then filtered result.But I am passing multiple values through parameter.
Ex: customerId may have (0,1,2) like that.In that case how to write the where condition.If i dont select any value just join it
ALTER PROCEDURE [dbo].[proc_get_akimbo_deails]
–@pi_country_id int,
–@pi_entity_id int,
@pi_customer_id int
–@pi_event_id int,
–@pi_cpgid int
–@from_date date,
–@to_date date
AS
BEGIN
select dh.akimbo_hid,cou.country_name,en.entity_name, c.customer_name,e.event_name,p.promo_type,dt.baseline,dt.co_packing,dt.pbi,dt.roi,dt.scc
from akimbo_data_header dh
inner join akimbo_customer_lookup c on c.customer_id=dh.customer_id
inner join akimbo_entity_lookup en on en.entity_id=c.entity_id
inner join akimbo_event_lookup e on e.event_id=dh.event_id
inner join akimbo_country_lookup cou on cou.country_id=en.country_id
inner join akimbo_data_transaction dt on dh.akimbo_hid=dt.akimbo_hid
inner join akimbo_cpg_lookup cp on cp.cpg_id=dt.cpg_id
inner join akimbo_promo_type_lookup p on p.promo_type_id=dt.promo_type_id
where dh.customer_id =case when dh.customer_id in(@pi_customer_id) then dh.customer_id in (@pi_customer_id) else dh.customer_id end
group by dh.akimbo_hid,cou.country_name,en.entity_name,
c.customer_name,e.event_name,p.promo_type,dt.baseline,dt.co_packing,dt.pbi,dt.roi,dt.scc
order by cou.country_name
END
On this example, I keep getting “Incorrect syntax near ‘>’ ”
Example:
DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END
I keep getting an error message when I use this example: “Incorrect syntax near ‘>’ ”
Example:
DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END
Is this the full code are you using? It does not have any character like >
Hi experts. Newbie here.
I’m trying to work out a select statement for sql svr 2008 that will eliminate credit for a 1/2 hour lunch break, if the person works less than 7 hours. The record for each worker has
worker.minimumhours=7
worker.lunchbreak = .5
I would like to use a select statement with a case/when/end – something like
DECLARE @StartDate AS DateTime
SET @StartDate = CAST(’03/25/2012′ AS DATE)
DECLARE @EndDate AS DateTime
SET @EndDate = CAST(’03/31/2012′ AS DATE)
SELECT
w.Firstname
,w.Lastname
,wf.Login
,wf.logout
,ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,2) AS [Hours]
,w.LunchDeduction AS [Lunch Deduction]
CASE DATEDIFF(hour, wf.Login, wf.Logout)
WHEN = wf.MinimumHours THEN ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,- 0,2)
END
AS [Billedhours]
FROM Workers AS w
JOIN Workflow AS wf
ON wf.LoggedInWorkerid = w.ID
JOIN Roles AS r
ON w.RoleID = r.RoleID
WHERE r.Descript = ‘Hourly’
AND wf.Login >= @StartDate AND wf.Logout <= @EndDate
but get the error
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'CASE'.
Wooo. That’s not what I have. sorry.
DECLARE @StartDate AS DateTime
SET @StartDate = CAST(’03/25/2012′ AS DATE)
DECLARE @EndDate AS DateTime
SET @EndDate = CAST(’03/31/2012′ AS DATE)
SELECT
w.Firstname
,w.Lastname
,wf.Login
,wf.logout
,ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,2) AS [Hours]
,w.LunchDeduction AS [Lunch Deduction]
CASE DATEDIFF(hour, wf.Login, wf.Logout)
WHEN = wf.MinimumHours THEN ROUND(CAST(DATEDIFF(MI, wf.Login, wf.Logout) AS DECIMAL)/60,- 0,2)
END
AS [Billedhours]
FROM Workers AS w
JOIN Workflow AS wf
ON wf.LoggedInWorkerid = w.ID
JOIN Roles AS r
ON w.RoleID = r.RoleID
WHERE (r.Descript = ‘Hourly’
OR r.Descript = ‘Salary’)
AND wf.Login >= @StartDate AND wf.Logout <= @EndDate
Hmm…. I have two when statements, that don’t seem to want to past in. Well it obviously doesn’t like the less then or great then signs, which should precede the wf.MinimumHours. I’m trying to check if their login and logout time difference in hours (to 2 decimal places) is less than wf.MinimumHours or greater than or equal to wf.MinimumHours.
Hi,
How can we include multiple case statement in single query
just simply separate the case statements with a comma(,)
Hi,
Is there any possibility to set the expressions to a smaller name in the query?
In the when clause of the searched case statement, if the column name is much larger to type every time, any solution for that?
Select
frmbrnchid,
case when frmbrnch =”
then
(select brnchname from @var1.dbo.brnchmaster as fbm where fbm.brnchid = p.frmbrnchid and fbm.compid = p.frmcompid)
else frmbrnch end as frmbrnchnm,
from currtrans as p where p.entryno = 4
i want to use @var1 in place of database name.. so please need a help for this..
sorry for the previous post.. this is what i want
my problem is that i need to use @var value as a database name… but i am not getting it.. this is what i am doing..
declare @var1 varchar(700)
set @var1 = (select compid from mastertables.dbo.compdet as fcd, CurrTrans as p where fcd.entryno = p.frmcompid)
Select
frmbrnchid,
case when frmbrnch =”
then
(select brnchname from @var1.dbo.brnchmaster as fbm where fbm.brnchid = p.frmbrnchid and fbm.compid = p.frmcompid)
else frmbrnch end as frmbrnchnm,
from currtrans as p where p.entryno = 4
i want to use @var1 in place of database name.. so please need a help for this..
This is bad for security reasons. Here is an example
declare @db varchar(20), @sql varchar(max)
select @db=’test’,@sql=”
select @sql=’select * from ‘+@db+’..dbo.brnchmaster’
exec(@sql)
if i didnt pass any date value it is taking by default 1900-01-01 00:00:00.000.
how to restrict that ? bcoz when i am displaying the value i dont display like that…..i wnt to display as empty blank. This i want to do in backend.
Any idea?
Declare @ToDt DateTime=’31-Jul-2012′
Declare @nSchID Numeric(18,0)=0
Declare @RptBrId Int=0
Declare @Id As Numeric=1
This Query Take more than 10 mins for fetching Data —
SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+’ ‘+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
WHERE CM.EntType<=202 AND TB.Id=@Id
AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt
But these query get only 1 sec.
SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+' '+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
WHERE CM.EntType<=202 AND TB.Id=@Id
–AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt
or
SELECT CM.cSchCode, CM.cSchName, CM.cAccCode, CM.Cust1Nm ,CM.cSchCode+' '+ CM.cSchName SchemFullName, TB.nOpBal, TB.nDebit, TB.nCredit, TB.nClBal, SC.cBranchName, CM.nCustID
FROM Customers CM INNER JOIN TrailBalance TB ON CM.nAcID =TB.nAcId
INNER JOIN SystemControl SC ON CM.nBranchId =Sc.nBranchId
WHERE CM.EntType<=202 AND TB.Id=@Id
AND CM.nBranchId BETWEEN CASE WHEN @RptBrId=0 THEN 0 ELSE @RptBrId END AND CASE WHEN @RptBrId=0 THEN 999 ELSE @RptBrId END
–AND ISNULL(CM.nSchID,0) BETWEEN CASE @nSchID WHEN 0 THEN 0 ELSE @nSchID END AND CASE @nSchID WHEN 0 THEN 999 ELSE @nSchID END
AND ((CM.cAcStatus’C’) OR (CM.dStatusDate>=@ToDt AND CM.cAcStatus=’C’)) AND CM.dOpeningDate<=@ToDt
i can't understand how this take.
please help me
Thanks in advance
I am not sure if I missed a question like this. I would like to test one field, and then perform CASE/comparison on another field.
I hope I can explain my thought clearly. I would like to test Delivery and based on its value, test Charge_Type.
One Order can have multiple charges associated with it.
In plain English:
If Delivery = ‘PrePay’
Then IF
test Charge_Type. If Charge_Type like “FR%”, then Charge_Amt as PrePay
test Charge_Type. If Charge_Type = “INS”, then Charge_Amt as Other
test Charge_Type. If Charge_Type = “NRG”, then Charge_Amt as Energy
ELSE Prepay = 0
If Delivery = ‘Collect’
Then IF
test Charge_Type. If Charge_Type like “FR%”, then Charge_Amt as Collect
test Charge_Type. If Charge_Type = “INS”, then Charge_Amt as Other
test Charge_Type. If Charge_Type = “NRG”, then Charge_Amt as Energy
ELSE Collect = 0
—
What I am trying to do is look at the freight types (I have 6) and then compare the Charge_Types (I have 10). Really, the INS/NRG – these will always go to one column. I guess it is the FR% and Delivery fields that would put values in another column.
I may have over complicated this. Too many if.then, but that was to try and explain the logic in plain English
I found that I can use a CASE within a CASE, and maybe this might help. The overall goal was to build a result set where I can have four derived columns AS depending on the value of Charge_Amt or Delivery fields. I will be aggregating the values too. This is a subselect of another one, so I would hope the subselect result would be be Order_num, PrePay, Collect, Energy, Other
Thank you for the explanation and examples. Saved my day. I used the select case sql in one my of programs and it worked wonders!