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 (http://blog.SQLAuthority.com)
j’ai utilisé la page, ça m’a bien servi, merci beaucoup
Hi,
Nice article , however, can you guide how we can manage the case statement when there are more then 600 options to select from.
in that case,,looping can be the better option,,what do I think..
Awais,
You could possibly Indent all the statements inside the Case Statement.
hi
m a newbie to SQL and trying to convert the following query to SQL
can u change this query to SQL update query using “case’ or “decode” by also providing me the detail explaination
UPDATE IMP_POSITIONS_NEW INNER JOIN POSITIONS
ON IMP_POSITIONS_NEW.JOBCODE = POSITIONS.POSITION_CODE
SET
POSITIONS.CUSTOM_MINIMUM = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRR_LOW],[IMP_POSITIONS_NEW].[MIN_RT_ANNUAL]),
POSITIONS.CUSTOM_MIDPOINT = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRP],[IMP_POSITIONS_NEW].[MID_RT_ANNUAL]), POSITIONS.CUSTOM_MAXIMUM = IIf([IMP_POSITIONS_NEW].[Grade]=’MBC’,[IMP_POSITIONS_NEW].[MRR_HIGH],[IMP_POSITIONS_NEW].[MAX_RT_ANNUAL]);
I am wondering if it is possible to do login in SQL Server like this
select *
FROM from employees
WHERE CASE WHEN a=1 THEN name = ‘Jim’ WHEN a=2 THEN EmployeeID = 90 WHEN a=4 THEN EmployeeStatus = ‘Fired’
can you see how im an trying to do a dynamic where clause is that possible with a case statement some how ?
You need to use this logic
select *
FROM from employees
WHERE
(a=1 and name = 'Jim') or
(a=2 and EmployeeID = 90) or
(a=4 and EmployeeStatus = 'Fired')
hi,
is it possible to use a case statement with the IN clause? for example below, how to use case statement without using the ‘if’ statement
if @type = 1
select * from table1 where code in (‘a’,'b’,'c’)
else
select * from table1
thx.
Use this
select * from table1
where ((code in ('a','b','c') and @type=1) or @type1)
My above reply should be
Use this
select * from table1
where ((code in ('a','b','c') and @type=1) or @type11)
BirdBuster, you can try this:
SELECT *
FROM employees
WHERE 1 = 1
and [name] = (CASE WHEN a=1 THEN ‘Jim’ else [name] end)
and EmployeeID = (case WHEN a=2 THEN 90 else EmployeeID end)
and EmployeeStatus = (case WHEN a=4 THEN ‘Fired’ else EmployeeStatus end)
Note that possible null values for name, EmployeeID, and EmployeeStatus are not handled for simplicity.
Thanks Prabhakar, your sample code lead me to the right track faced with a similar situation. Can you give me a quick example of how you would handle the NULL values in this situation?
Thanks…
Tony W,
case function can be used with the IN clause, but it’s limited (the result expression can only return one value). In other words, something like the following (slightly modified version of your original) can be rewritten.
if @type = 1
select * from table1 where code in (’a’) –code=’a’ in essence
else
select * from table1
–rewritten using the case
select * from table1 where code in (case when @type = 1 then ‘a’ else code end)
So, to rewrite your original statement without the ‘if’, here’s an ugly version:
select * from table1 where
code in (case when @type = 1 then ‘a’ else code end)
or code in (case when @type = 1 then ‘b’ else code end)
or code in (case when @type = 1 then ‘c’ else code end)
But you cannot use the following:
select * from table1 where
code in (case when @type = 1 then (select ‘a’ union select ‘b’ union select ‘c’) else code end)
This will return the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
Your article is greatly appreciated!!!!!!!!!!!
How do you include an update statement in a case statement xmlDocument…….
eg.
CASE WHEN (@xml.exist(‘/ROOT/Trip/RouteItem[@RouteDBId="875077"]‘)) THEN
UPDATE route_details
SET
route_details.trip_authority_code = XMLRouteDetails.TripAuthorityNumber,
route_details.start_route_location_name = XMLRouteDetails.StartLocationName,
route_details.Start_Date = XMLRouteDetails.StartDate
From OpenXml(@intPointer,’/ROOT/Trip/RouteItem’,3)
With
(
TripAuthorityNumber int ‘../@TripAuthorityNumber’,
StartLocationName varchar(100),
StartDate smalldatetime
XMLRouteDetails WHERE route_details.trip_authority_code = XMLRouteDetails.TripAuthorityNumber
– Rollback the transaction if there were any errors
IF @@ERROR 0
BEGIN
– Rollback the transaction
ROLLBACK
– Raise an error and return
RAISERROR (‘Error in updating route in route_details table’, 16, 1)
RETURN
END
ELSE
INSERT INTO route_details
(
trip_authority_code,
start_route_location_name,
Start_Date
)
Select
@TripID,
StartLocationName,
StartDate
from OpenXml(@intPointer,’/ROOT/Trip/RouteItem’,3)
With
(
TripAuthorityNumber int ‘../@TripAuthorityNumber’,
StartLocationName varchar(100),
StartDate smalldatetime
)
– Rollback the transaction if there were any errors
IF @@ERROR 0
BEGIN
– Rollback the transaction
ROLLBACK
– Raise an error and return
RAISERROR (‘Error in inserting route into route_details table’, 16, 1)
RETURN
END
END
COMMIT
exec sp_xml_removedocument @intPointer
RETURN
I am passing two different parameters 1>@Name 2>@Id. Depending on parameter @Name I want to fire select statement on different table. Can I put this in case statement,if yes then how?
for eg:
@name= text1
@Id=87
select col1 from tab1 where id=@Id
@name=text2
@Id=85
select col2 from tab2 where id=@Id
Thanks in advance.
No. You need to use IF clause for this
Not working ,
SELECT
case when (ResheduleDate = ”) then AppDate else ResheduleDate end as FinalDate
from tbl_Appointments
WHERE ProviderID = 76 and Status = 2 Group by FinalDate
Thanks
What did you mean by not working?
Use NULL in place of ”
I am trying to use a 2 case statements inside one table. both statements work but they dont work together, any ideas??
Sunil ( with dollar sign)
Please write your query here….
Thanks,
IM
I need a return of two values from a case statement as here i am searching for a value in database. when a perticular value is found i need 2 vaues in my result set.
Pls suggest?
CASE would return only one value
Post your actual requirement
I have the following CASE Statement . I did it manually. how can I make it dynamic ?
CASE TourismFilmHeader.RefLanguageID
WHEN ’1′ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘Arabic’)
WHEN ’2′ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘English’)
WHEN ’3′ then (SELECT TourismLanguageMaster.Language FROM TourismLanguageMaster WHERE Language = ‘Hindi’)
ELSE ‘None’
END
[...] SQL SERVER – CASE Statement/Expression Examples and Explanation [...]
informative article
hi
Pls give me sql 2008 Note
hi chalana send me ur number i will give u note
Hi,
How can we do this
Select id, name,
CASE
WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE AGE END
from users
I am trying to run case on a computed field and want to show a text incase the output of that field meets the criteria or simply echo the output.
Cannot use the field computation again, as it includes a subquery, which I don’t think should run again for performance reasons.
Thanks
Use derived table
select * from
(
Select id, name,
CASE
WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE AGE END as AGE
from users
) as t
where AGE='some condition'
Also it is not good practice to convert numbers to text
@Hitendra
Can you rephrase your question please.
I could not understand your question.
If you want to pass column name as parameter, then you can do that using dynamic SQL.
Before I suggest you something, I want to make sure I understand your question. Please put your question in very clear form.
Thanks.
~ IM.
How about this?
Select id, name
CASE WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE cast(AGE as varchar(5)) END as NewAge
from users
Where NewAge would return a string. or:
Select id, name, Age,
CASE WHEN AGE+@SomeVariable<17 THEN ‘Young’
ELSE ” END as SubjectiveAge
from users
Where Age would contain the age, and SubjectiveAge would contain ‘Young’ or an empty string.
Nice article. Can you pls tell me how to insert a null value in the database while mentioning the field name in the inser query.
i am getting value from textBox of non-required field user may not key-in any value.
pls guide me the way to write a stored procedure to come across this issue
Just use NULL
I am attempting to update table1.status with a 1 if it exists within table2 otherwise i want it to update with a 0 if it does not. the statement I have is as follows
Update
[table1]
SET Status = (
case
when
(Select status from table1 inner join table2
on table1.ID = table2.ID)
then ’1′
else ’0′
you need this at the end of your code
end
)
I am getting problem in removing special character from string
I am doing like this :I want to do multiple sepcial charcter remove from this but i am able to replace only on charcter
declare @name varchar(10)
declare @currentchar varchar(10)
declare @backUp varchar(10)
declare @len int
declare @i int
set @name=’;kri;pal’
set @len=len(@name)
set @i=1
declare @Kk varchar(20)
WHILE @i <= @len
BEGIN
SET @currentchar = SUBSTRING(@name,@i, 1)
select
@backUp =(case when (@currentchar=';' )then
replace(@currentchar,';' ,'')
else
@currentchar end)
set @kk=isnull(@kk,'')+@backUp
SET @i = @i + 1
END
pls help me urgent:
Refer this post and use the approach described there
http://beyondrelational.com/blogs/madhivanan/archive/2010/01/08/replace-data-of-one-table-with-data-of-other-table.aspx
@krish
Why not use REPLACE() for the entire string? No WHILE loop or anything, just a simple REPLACE(@name, ‘;’, ”)
Sub: CASE Statement in SQL server
Can you please tell me that
CASE Statement working with String / Varchar
means i want to check some varchar values
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE
WHEN @TestVal =’Asma’ THEN ‘Top 1′
ELSE ‘Other’
END
@Asma,
Ofcourse it will work.
Tejas
@Asma Qureshi
What is the problem?
Also, you can use a simple case expression:
DECLARE @TestVal varchar
SET @TestVal = ‘Asma’
SELECT
CASE @TestVal
WHEN ‘Asma’ THEN ‘Top 1′
ELSE ‘Other’
END
Hi Experts,
Is anyway i can write following query in Case statement? please guide me
(
( ( @Variable(‘EntityType’) = ‘DEPARTMENT’ ) AND ( @Variable(‘EntityCode’) = ‘–ALL–’ ) )
OR ( (@Variable(‘EntityType’) = ‘DEPARTMENT’) AND(@Variable(‘EntityCode’) != ‘–ALL–’ ) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) = ‘–ALL–’))
OR ((@Variable(‘EntityType’) = ‘RAILROAD’) AND (@Variable(‘EntityCode’) !=’–ALL–’) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) = ‘–ALL–’))
OR ((@Variable(‘EntityType’) = ‘DIVISION’) AND (@Variable(‘EntityCode’) !=’–ALL–’) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–’))
OR ((@Variable(‘EntityType’) = ‘FUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–’) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) = ‘–ALL–’))
OR ((@Variable(‘EntityType’) = ‘SUBFUNCTION’) AND (@Variable(‘EntityCode’) !=’–ALL–’) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) = ‘–ALL–’))
OR ((@Variable(‘EntityType’) = ‘RESCEN’) AND (@Variable(‘EntityCode’) !=’–ALL–’) AND (LTRIM(RTRIM(Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD)) IN (select * from ufn_multi_value_to_tbl(@Variable(‘EntityCode’)))))
)
Thank you
PV
@PV
How’s this?
WHERE
@Variable(‘EntityCode’) = ‘–ALL–’
OR LTRIM
(
RTRIM
(
CASE (@Variable(‘EntityType’)
WHEN ‘DEPARTMENT’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DEPT_CD
WHEN ‘RAILROAD’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RAILROAD_CD
WHEN ‘DIVISION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.DIVISION_CD
WHEN ‘FUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.FUNCTION_CD
WHEN ‘SUBFUNCTION’ THEN Alias_dbo_SAF_ORG_STRUCTURE.SUBFUNCTION_CD
WHEN ‘RESCEN’ THEN Alias_dbo_SAF_ORG_STRUCTURE.RESCEN_CD
END
)
) IN (SELECT * FROM ufn_multi_value_to_tbl(@Variable(‘EntityCode’)
Hello Experts, i want to set a variable and use the value later for processing, i am getting an error when using the below..
SELECT
CASE @TestVal
WHEN 1 THEN set @var= ‘First’
WHEN 2 THEN Set @var= ‘Second’
WHEN 3 THEN set @var= ‘Third’
ELSE ‘Other’
END
===================
insert into table (@var, etc….)
Hi Anil,
Correct syntax to assign a value to variable with CASE statement is:
SELECT @var=
CASE @TestVal
WHEN 1 THEN set ‘First’
WHEN 2 THEN Set ‘Second’
WHEN 3 THEN set ‘Third’
ELSE ‘Other’
END
Thanks,
Tejas
SQLYoga.com
Hello Anil,
Use the SELECT CASE as below:
SELECT @var =
CASE @TestVal
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
ELSE ‘Other’
END
Regards,
Pinal Dave
Dear All
Kindly correct the given below part of query.
SELECT distinct(P1.DOCNUM),P6.DOCENTRY,
Case when P1.FOOTER = NULL THEN
P1.U_POTRD , P1.U_EXARW , P1.U_ConNum as ContainerNo, P1.U_Vessel_Flight_no as VesselNo, ,P1.U_CDIBOL as BLNumber, cast(P1.FOOTER as varchar(max))as FOOTER ,
ELSE
P5.U_POTRD , P5.U_EXARW , P5.U_ConNum as ContainerNo, P5.U_Vessel_Flight_no as VesselNo, ,P5.U_CDIBOL as BLNumber, cast(P5.FOOTER as varchar(max))as FOOTER,
Endcase
——-
Problem with case part.
With Regards,
Nirbhay
Use
P1.FOOTER IS NULL
instead of
P1.FOOTER = NULL
I’m a novice (if that). I’m trying to use MS Query to send data to Excel and I’m only trying to pull ACTNCNT when ACTNCNT is >0, ELSE ESTNCNT.
In Excel it would be =IF (ACTNCNT > 0, ACTNCNT, ESTNCNT). In Crystal, I would be able to create a formula field I could show the output for – NCNT; IIF (ACTNCNT > 0, ACTNCNT, ESTNCNT).
Is there anyway to pass “NCNT” to Excel? Or do I need to pull both and run a macro?
I’m also having issue using a parameter. When I’m in MS Query I can use OCCURRED>=[From Date]AND=#1/28/2010 6:00:00 AM# And <=#1/29/2010 6:00:00 AM#"; everything works.
Any thoughts?
@Jason
Your question is not clear.
Are you trying to export data from MS SQL Server to excel sheet. What tool are you using, like SSIS/DTS/openquery ?
Writing a case statement in DTS/SSIS data flow task is very easy.
Please explain clearly what is your requirement.
~ IM
Im trying to use 2 CASE statements together in a SELECT query without much luck.
The second CASE uses the output from the first CASE as it’s expression (well it’s supposed to).
SELECT
accountNum
, balanceCalculation = case WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
, balanceDescription = CASE WHEN balanceCalculation > 6000 THEN ‘Large Balance’
ELSE ‘small balance’
END
FROM tblFinances
@Ross
Check if this helps.
SELECT accountNum
, balanceCalculation = CASE
WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
, balanceDescription = CASE WHEN (
CASE
WHEN Balance > 5000 THEN Balance *2
ELSE Balance /2
END
) > 6000 THEN ‘Large Balance’
ELSE ’small balance’
END
FROM tblFinances
~ IM.
How do I use case when Im want to compare not only the column under question but also another column that has a said value.
For example table a has col1, col2, col3. Now when I want to see what is stored in Col1 in conjunction with Col2, how do I do that.
For example
Col1 Col2 Col3
1 5 44
2 6 12
3 ” 1
3 A 3
Now here, I want to update the value of Col3 under the following conditions –
1. When Col3 value = 1 and
2. When Col2 value = Null then set the value of Col3 to Null.
How would I do this ?
Thanks,
Tim K Liu
@Tim k
SET Col3 = CASE WHEN Col3 = 1 AND Col2 IS NULL THEN NULL ELSE Col3 END
Or, the inverse:
SET Col3 = CASE WHEN Col3 1 AND Col2 IS NOT NULL THEN Col3 END
Although, this could usually be done in ther WHERE clause:
UPDATE mytab SET Col3 = NULL WHERE Col3 = 1 AND Col2 IS NULL;
Thank You Brian Tkatch !
I figured the second one by myself but I followed your firststep as it is a bit more readable & understandable.
@Tim
Yeah, that’s why i put it there. :) The second one is cleaner, the first one is easier.
Note that the third option is probably best, as the CASE will UPDATE every single record, either to NULL or to itself. If it is in the WHERE clause, it will only UPDATE the records that need changing.
This is beneficial because:
1) In the first case an AFTER UPDATE TRIGGER will fire even if the record is SET to itself.
2) For a large TABLE, setting a small subset is usually faster. The CASE would UPDATE every record, so it might take longer.
3) If another process has the row locked, and this UPDATE needs to change the value, that is fine. But if it doesn’t need to change it, and it just sets it to itself, the CASE can cause a wait until the record is no longer locked (or timesout).
hi am trying to do a consultation with case and brand me a mistake, could you help please???
excuse the grammar, do not speak English
hi am trying to do a consultation with case and brand me a mistake, could you help please???
excuse the grammar, do not speak English
USE mantto1;
GO
DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
SET ANSI_WARNINGs OFF
SELECT @DiaReporte = ’9/02/2010′, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
FROM avances , actividades
where actividades.clave=avances.clave
group by actividades.fecha_i_p
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
GO
sorry, this is the qerry, i have a mistake but i don’t know what is it :(
USE mantto1;
GO
DECLARE @DiaReporte datetime, @fir datetime, @diasR as int;
SET ANSI_WARNINGs OFF
SELECT @DiaReporte = ’9/02/2010′, @fir=min(fa_real), @diasR=datediff (dd,actividades.fecha_i_p,@fir)
FROM avances , actividades
where actividades.clave=avances.clave
group by actividades.fecha_i_p
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
GO
select case when @diasR>0 then
SELECT @DiaReporte as fecha_reporte, @fir as fecha_inicial_r, @diasR as dias_retraso
Probably should be:
IF @diasR > 0 SELECT @DiaReporte AS fecha_reporte, @fir AS fecha_inicial_r, @diasR AS dias_retraso
hi,
Wanted to know if we can use “IN” in case statement like
case @variable
when in (1,2,3) then
when in (4,5,6) then…
Thanks,
Nirav
Hi,
That’s almost correct. Try this:
CASE
WHEN @variable IN (1,2,3) THEN ..
WHEN @variable IN (4,5,6) THEN ..
END
Thanks Polelo
Hi Experts,
I have a table PRD with the following value:
ComCod WHLOC
1 Null
2 Null
2 SLC1
I wish to update
1. WHLOC with “SPL” where comcod = ’1′ and WHLOC is empty.
2. WHLOC with “SCM” where comcod =’2″ and WHLOC is empty.
I wrote the following case statement but it is invalid.
UPDATE prd SET WHLoc =(CASE WHEN (ComCod = ’1′ and ISNULL(whloc,”)=” THEN ‘SPL’ )
CASE WHEN (ComCod = ’2′ and ISNULL(whloc,”)=”) THEN ‘SCM’ ELSE END )
Thanks in advance for any help/advice!
@Andrea
How’s this?
UPDATE
prd
SET
WHLoc = CASE comcod
WHEN 1 THEN ‘SPL’
WHEN 2 THEN ‘SCM’
END
WHERE
whloc IS NULL;
Hi Brian,
Thanks for your help!
Your advice works great for my problem!
rgds/Andrea
Hello,I am a 13 year old boy,Visual Basic is in my sylabbus,Please contact me on chitu_smarty@yahoo.co.in
Hi is it posible to write something like
Case
when name in (‘john’, ‘ben’, ‘david’) then male else female end
thanks in advance
@Thato
Yes. But male male and female should also be in quotes.
DECLARE @heads VARCHAR(MAX)
select
case when div.Name = ‘Europe’ THEN
(SELECT COALESCE(@heads+ ‘ , ‘,”) + dbo.PersonName(em1.EmpID) from
(
/Complex Select stat to get
) as em1) else ” END as EUROPE
from Division div
I get error while using COALESCE expression in THEN clause of WHEN in CASE.
can you tell me what am I missing?
You need to use IF clause instead of CASE Expression
use[ramesh]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author:
– Create date:
– Description:
– =============================================
CREATE PROCEDURE SP_Emp
@salary int
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
Update employee set salary =
CASE
WHEN @salary =1000
salary+100
Else
Salary+10;
END
Above code i am getting problem
Msg 102, Level 15, State 1, Procedure SP_Emp, Line 17
Incorrect syntax near ‘salary’.
Hello Ramesh,
You are missing something here:
WHEN @salary =1000
salary+100
Else
Salary+10;
I think you want to write it as below:
WHEN @salary =1000 THEN salary+100
Else Salary+10;
Regards,
Pinal Dave
Update employee set salary =salary+
CASE
WHEN @salary =1000
100
Else
10
End;
Hellow Pinal Dave can we use CASE WHEN in Stored procedure? please explain with one example. i have written one code but i am getting error
My Code is:
CREATE procedure sp_Products
(
@ProductId nvarchar(max),
@Catalog nvarchar(100)
)
as
begin
case @Catalog
when ‘Courseware’
then select * from tblProducts where ProductId in(@ProductId)
when ‘Options’
then select * from tblorder where ProductId in(@ProductId)
end
end
CASE expression wouldn’t work that way
Use IF statement
CREATE procedure sp_Products
(
@ProductId nvarchar(max),
@Catalog nvarchar(100)
)
as
begin
if @Catalog='Courseware'
select * from tblProducts where ProductId in(@ProductId)
else if @Catalog='Options'
select * from tblorder where ProductId in(@ProductId)
end
ThanQ Madhivanan
i have 8 column in my table and someone contain 0 and another contain value
i want to check smaller value between all rows and display it in another column
what i do for exclude column that contain zero
plz help me
Post some sample data with expected result
Try This one it may work
select min(YourColumnName) as MinimuValue from tablename where YourColumnName > 0
I need to add an additional statement to include :
CASE WHEN mtmatter IN (SELECT
from matter, mattimhs
where mclient = ’99991′
and matter.mmatter = mattimhs.mtmatter
Is there a way to combine this with the following statement?
ALTER VIEW Non_Billable_Breakdown AS
SELECT mttk AS nbb_tk,
mtper AS nbb_per,
mthrnbdw AS nbb_tothr,
CASE WHEN mtmatter = ’09999-006600′ THEN mthrnbdw ELSE 0 END AS nbb_admin,
CASE WHEN mtmatter = ’09999-006605′ THEN mthrnbdw ELSE 0 END AS nbb_comply,
CASE WHEN mtmatter = ’09999-006610′ THEN mthrnbdw ELSE 0 END AS nbb_commact,
CASE WHEN mtmatter = ’09999-006620′ THEN mthrnbdw ELSE 0 END AS nbb_educ,
CASE WHEN mtmatter = ’09999-006630′ THEN mthrnbdw ELSE 0 END AS nbb_clidev,
CASE WHEN mtmatter = ’09999-006631′ THEN mthrnbdw ELSE 0 END AS nbb_emplawconf,
CASE WHEN mtmatter = ’09999-006640′ THEN mthrnbdw ELSE 0 END AS nbb_probono,
CASE WHEN mtmatter = ’09999-006642′ THEN mthrnbdw ELSE 0 END AS nbb_general,
CASE WHEN mtmatter = ’09999-006650′ THEN mthrnbdw ELSE 0 END AS nbb_personal,
CASE WHEN mtmatter = ’09999-006660′ THEN mthrnbdw ELSE 0 END AS nbb_lobby,
CASE WHEN mtmatter = ’09999-006680′ THEN mthrnbdw ELSE 0 END AS nbb_legalsvcs,
CASE WHEN mtmatter = ’09999-080920′ THEN mthrnbdw ELSE 0 END AS nbb_genimm,
CASE WHEN mtmatter = ’09999-006606′ THEN mthrnbdw ELSE 0 END AS nbb_profact,
CASE WHEN mtmatter = ’09999-006607′ THEN mthrnbdw ELSE 0 END AS nbb_recruitting,
CASE WHEN mtmatter = ’09999-006615′ THEN mthrnbdw ELSE 0 END AS nbb_iGreenLaw,
CASE WHEN mtmatter = ’09999-006618′ THEN mthrnbdw ELSE 0 END AS nbb_economicclarity
FROM mattimhs
@Debbie
Now sure what you want, so i’ll guess. I think it can be EXISTS. Also, all those CASE statements can use the simple-expression. It’s slightly clearer.
SELECT
mttk nbb_tk,
mtper nbb_per,
mthrnbdw nbb_tothr,
CASE mtmatter WHEN ’09999-006600′ THEN mthrnbdw ELSE 0 END nbb_admin,
CASE mtmatter WHEN ’09999-006605′ THEN mthrnbdw ELSE 0 END nbb_comply,
CASE mtmatter WHEN ’09999-006610′ THEN mthrnbdw ELSE 0 END nbb_commact,
CASE mtmatter WHEN ’09999-006620′ THEN mthrnbdw ELSE 0 END nbb_educ,
CASE mtmatter WHEN ’09999-006630′ THEN mthrnbdw ELSE 0 END nbb_clidev,
CASE mtmatter WHEN ’09999-006631′ THEN mthrnbdw ELSE 0 END nbb_emplawconf,
CASE mtmatter WHEN ’09999-006640′ THEN mthrnbdw ELSE 0 END nbb_probono,
CASE mtmatter WHEN ’09999-006642′ THEN mthrnbdw ELSE 0 END nbb_general,
CASE mtmatter WHEN ’09999-006650′ THEN mthrnbdw ELSE 0 END nbb_personal,
CASE mtmatter WHEN ’09999-006660′ THEN mthrnbdw ELSE 0 END nbb_lobby,
CASE mtmatter WHEN ’09999-006680′ THEN mthrnbdw ELSE 0 END nbb_legalsvcs,
CASE mtmatter WHEN ’09999-080920′ THEN mthrnbdw ELSE 0 END nbb_genimm,
CASE mtmatter WHEN ’09999-006606′ THEN mthrnbdw ELSE 0 END nbb_profact,
CASE mtmatter WHEN ’09999-006607′ THEN mthrnbdw ELSE 0 END nbb_recruitting,
CASE mtmatter WHEN ’09999-006615′ THEN mthrnbdw ELSE 0 END nbb_iGreenLaw,
CASE mtmatter WHEN ’09999-006618′ THEN mthrnbdw ELSE 0 END nbb_economicclarity,
CASE WHEN EXISTS(SELECT * FROM matter WHERE mclient = ’99991′ and matter.mmatter = mattimhs.mtmatter)
THEN 1 ELSE 0
END
FROM
mattimhs;
select DonationId,KinName,DonationType,
CASE DonationType
WHEN 1 THEN (SELECT VDNo FROM Exchange_VD WHERE DonationId=’aa’)
WHEN 3 THEN (select PatientId from View_PatientInfo VP INNER JOIN Billing_Master BM ON BM.RequestId=VP.RequestId INNER JOIN
Exchange_POR ER ON ER.IssueId=BM.Id WHERE ER.PORNo=’POR1011/1/3′)
ELSE ‘no’
END
from View_DonorInformation
This wont work if the subquery returns more than one row
I have a problem to insert data in a table. when two user on diffrent site want to insert data into same table, error thrown by system.
Another Problem is that : I fatch Max(columnName) and max value+1 my next value for insert statement , Now point is that when two user aceess same max value and insert same value into table than a confliction or violation of primary key error thrown by software.
plz plz plz solve these problem. And thanx in advance…
You need to make use of a trasaction to avoid the error. Aslo just before adding data to table query for max(col)+1
select dm01_cust,dm01_site,dm01_date,DM01_INVC,DM01_SSIT,DM01_PBLE,DM01_BILLINDV,DM01_CONTRACT,DM01_AMOUNT,
(case
when dm05a_link is not null then
‘Service ‘+cast(DM05A_AMNT as varchar(18))
when dm05b_link is not null then
‘Service ‘+cast(DM05b_AMNT as varchar(18))
when dm05l_link IS not null then
‘Service ‘+cast(DM05l_AMNT as varchar(18))
when dm05z_link IS not null then
‘Service ‘+cast(DM05Z_AMNT as varchar(18))
else 0 end)
–,COUNT(*)
from TRUX_BV_COMP.dbo.dm01
left join TRUX_BV_COMP.dbo.dm05a
on DM01_ID=dm05a_link
left join TRUX_BV_COMP.dbo.dm05b
on DM01_ID=dm05b_link
left join TRUX_BV_COMP.dbo.dm05l
on DM01_ID=dm05l_link
left join TRUX_BV_COMP.dbo.dm05z
on DM01_ID=dm05z_link
where DM01_DATE=’2004-08-31′
and DM01_CUST=9460
conversion failed when converting the varchar value to data type int
Nice, but explanation is too short and not describe complex queries.
need to explain some complex queries using case.
Refer this post to know more examples about the CASE expression
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/24/case-expression.aspx
I have a scenario where I want to assign the value of column c2 to a seperate variable based on the value of column c1 in the same query. So if the value of c1 is 1, then the value of c2 should go be assigned to v1, if the value of c1 is 2, then the value of c2 should be assigned to variable v2 and so on. I tried the below query, but it gives a syntax error. Any help is appreciated.
SELECT CASE WHEN CURRENCY_CODE = ‘USD’ THEN @USD_sec_alias = SECURITY_ALIAS
WHEN CURRENCY_CODE = ‘GBP’ THEN @EUR_sec_alias = SECURITY_ALIAS
WHEN CURRENCY_CODE = ‘EUR’ THEN @EUR_sec_alias = SECURITY_ALIAS
END
FROM SECURITY.DBO.SECURITY_MASTER
WHERE CURRENCY_CODE IN (‘USD’,'GBP’,'EUR’)
AND INVESTMENT_TYPE = ‘CASH’
Thank you,
Chintan Somaiya
I’m trying to create a case statement in the Where Clause, but I keep getting an eror in the statement.
What I’m trying to do is Run it so that if it is pulling after a specific date to filter out sppecific Codes:
Select *
From Table as a
Where
Code = Case when Date > ’10-02-2010′ then Code Not in (1,2,3,4) Else Code not in(5,6,7,8) End.
I keep getting the following error:
Incorrect syntax near the keyword ‘in’.
Any thoughts?
Try this
Select *
From Table as a
Where
(Code Not in (1,2,3,4) and Date > ’10-02-2010′ )
OR
(Date <- ’10-02-2010′ and Code not in(5,6,7,8))
i mtrying to convert the nvarchar datatype in to numeric.
but it showing error.
“Error converting data type nvarchar to numeric.”
tell me how to convert this.
Post the code you used
Hi,
Would you be able to help me in an SQL query? The process is currently performing the following query.
SELECT A,B,C,D
FROM TableA
WHERE Date_First_Invoiced_AR NOT BETWEEN MM/DD/YY AND MM/DD/YY
I need to change it so that The Date_First_Invoiced_AR is from TableB, if it exists, and if not then use TableA.
I tried the following, but it did not work.
SELECT A,B,C,D,
CASE WHEN TableB.Date_First_Invoiced ” THEN TableB.Date_First_Invoiced_AR ELSE TableA.Date_First_Invoiced_AR END AS DateInvoiced
FROM TableA
LEFT OUTER JOIN TableB ON TableA.ID = TableB.ID
WHERE DateInvoiced NOT BETWEEN MM/DD/YY AND MM/DD/YY
I get an error message ‘Invalid column name ‘DateInvoiced’.
Any help would be GREATLY appreciated!!
Hi pinal.
could u please help mi on this query.
SELECT e.Name +’ ‘ + e.Surname as Name
,count ([ID_EmployeeTask]) as workinkDays
,sum ([FinalWT]) as PeriodTotalWT
,sum ([FinalPoints]) as PeriodTotalpoints
,sum ([FinalWT])/count ([ID_EmployeeTask]) as AverageWT
,sum ([FinalPoints])/count ([ID_EmployeeTask]) as ProductivityPerPeriod
,’ ProductivityperHour’ =(case when sum ([FinalWT])= 0 then 0 else sum ([FinalPoints])/sum ([FinalWT])End)
FROM [OPS_ReportingDB].[Organization].employee e
inner join [OPS_ReportingDB].[TaskData].[EmployeeTask]
on [OPS_ReportingDB].[TaskData].[EmployeeTask].[ID_Employee] = e.[ID_Employee]
where (e.id_Employee in –(select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department inner join Organization.Division I on D.ID_Division =I.ID_Division and ID_HeadOfDivision = 2 ))
(case
when 52 in (select ID_TeamLeader from [OPS_ReportingDB].Organization.Unit)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit and ID_TeamLeader = 52)
when 52 in (select ID_HeadOfDepartment from [OPS_ReportingDB].Organization.Department)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department and ID_HeadOfDepartment = 52 )
when 52 in (select ID_HeadOfDivision from [OPS_ReportingDB].Organization.Division)
then (select e.id_Employee from Organization.Employee e inner join Organization.Unit u on e.ID_Unit =u.ID_Unit inner join Organization.Department D on u.ID_Department= D.ID_Department inner join Organization.Division I on D.ID_Division =I.ID_Division and ID_HeadOfDivision = 52 )
else (0) end) )
group by e.[ID_Employee] ,e.Name , e.Surname order by Name
regards
You need to post what the problem with your query
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
Hi Mani,
Please check this solution http://blog.sqlauthority.com/2007/07/08/sql-server-fix-error-msg-244-level-16-state-1-fix-error-msg-245-level-16-state-1/
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!
How to use save points in a loop. Say for example I have 100 records and Iam processes records in batches of 10. when Iam processing 15 record I get a error. but at this state I need to commit 11 to 14 record. Can you please help me in providing a solution
Hi,
I want to use case statement in having clause. Is below correct?
GROUP BY D.[LOGIN],D.[BOOKED DATE]
HAVING (CASE WHEN Datepart(Weekday, D.[BOOKED DATE])=4 THEN SUM(D.[EFFORT]) < 510 ELSE SUM(D.[EFFORT]) < 480 END)
I want to show the records, whose sum is <510 on weekdays and sum<480 on 4th day of the week.
Can anyone please suggest, how to write above query.
Try
GROUP BY D.[LOGIN],D.[BOOKED DATE], Datepart(Weekday, D.[BOOKED DATE])
HAVING Datepart(Weekday, D.[BOOKED DATE])=4 AND SUM(D.[EFFORT]) < 510 OR Datepart(Weekday, D.[BOOKED DATE])4 AND SUM(D.[EFFORT]) < 480
Thank you Madhivanan :)
I want to implement this condition in sql
case when @LOB=’ABC’ then LOB=’ABC’ else LOB’ABC’ end
Create PROCEDURE [dbo].[spRefreshIndex]
@tblName varchar(100)
,@varOutPut VARCHAR(100) OUTPUT
,@scr varchar(2000)
AS
BEGIN
SELECT @scr = CASE @tblName
WHEN TBL_ACCREDITED_COMPANY THEN ‘ALTER FULLTEXT INDEX ON TBL_ACCREDITED_COMPANY START FULL POPULATION’
END
END
please help..
i have an error of invalid column name
tnx
select e.AC_NO || e.PART_NO || s.SECTION_NO || s.Section_name_Hi || s.Section_Name_En || a.DIST_NO || d.DIST_NAME || d.DIST_NAME_E || p.Policest_Name || p.Policest_Name_En || su.SubDivision_ID || t.Tahsil_Name ||t.Tahsil_Name_En as address
from EPICDB.dbo.EPIC as e inner join AC_008.dbo.sec_detail as s
on s.PART_NO = e.Part_No inner join erollcontrol.dbo.AC_List as a
on e.AC_NO = a. AC_NO inner join EPICDB.dbo.DISTRICT as d
on d.DIST_NO = a.DIST_NO inner join erollcontrol.dbo.Policest as p
on s.CCode = p. ccode inner join erollcontrol.dbo.SubDivisions as su
on s.CCode = su.ccode inner join erollcontrol.dbo.Tahsils as t
on su.ccode = t.ccode order by AC_NO
hi,
here am porting valuing from multiple databases multiple tables and i want to port all these values into destination table EPIC for the field address.. i m getting output wth different columns but i want to concatenate all the values and send to address field..
[...] CASE Statement/Expression Examples and Explanation 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. [...]