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 expressionWHEN
expression1 THEN expression1
[[WHEN
expression2 THEN expression2] [...]]
[ELSE expressionN]END
Example:DECLARE @TestVal
INT
SET @TestVal = 3SELECTCASE
@TestValWHEN 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:CASEWHEN
Boolean_expression1 THEN expression1
[[WHEN
Boolean_expression2 THEN expression2] [...]]
[ELSE
expressionN]END
Example:DECLARE @TestVal
INT
SET @TestVal = 5SELECTCASEWHEN
@TestVal <=3 THEN 'Top 3'ELSE 'Other'END
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]);
Reply
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')
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.
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 thisNot 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??
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
Pls give me sql 2008 Note
hi chalana send me ur number i will give u note
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.
Reply
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
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:
Reply
@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 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
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
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’)
Reply
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
Reply
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?
Reply
@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
Reply
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
Reply
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.
Reply
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
Reply
@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;
Reply
Thank You Brian Tkatch !
I figured the second one by myself but I followed your firststep as it is a bit more readable & understandable.
Reply
@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).
Reply
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
Reply
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
Reply
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
Reply
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
Reply
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
Reply
Hi,
That’s almost correct. Try this:
CASE
WHEN @variable IN (1,2,3) THEN ..
WHEN @variable IN (4,5,6) THEN
..
END
Reply
Thanks Polelo
Reply
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!
Reply
@Andrea
How’s this?
UPDATE
prd
SET
WHLoc = CASE comcod
WHEN 1 THEN ‘SPL’
WHEN 2
THEN ‘SCM’
END
WHERE
whloc IS NULL;
Reply
Hi Brian,
Thanks for your help!
Your advice works great for my problem!
rgds/Andrea
Reply
Hello,I am a 13 year old boy,Visual Basic is in my sylabbus,Please contact me on chitu_smarty@yahoo.co.in
Reply
Hi is it posible to write something like
Case
when name in (‘john’, ‘ben’, ‘david’) then male else female end
thanks in advance
Reply
@Thato
Yes. But male male and female should also be in quotes.
Reply
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?
Reply
You need to use IF clause instead of CASE Expression
Reply
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’.
Reply
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
Reply
Update employee set salary =salary+
CASE
WHEN @salary
=1000
100
Else
10
End;
Reply
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
Reply
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
Reply
ThanQ Madhivanan
Reply
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
Reply
Post some sample data with expected result
Reply
Try This one it may work
select min(YourColumnName) as MinimuValue from tablename where YourColumnName > 0
Reply
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
Reply
@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;
Reply
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
Reply
This wont work if the subquery returns more than one row
Reply
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…
Reply
You need to make use of a trasaction to avoid the error. Aslo just before adding data to table query for max(col)+1
Reply
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
Reply
Nice, but explanation is too short and not describe complex queries.
need to explain some complex queries using case.
Reply
Refer this post to know more examples about the CASE expression
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/24/case-expression.aspx
Reply
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
Reply
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?
Reply
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))
Reply
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.
Reply
Post the code you used
Reply
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!!
Reply
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
Reply
You need to post what the problem with your query
Reply
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,
Reply
You need to post some sample data with expected result
Reply
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
Reply
Correction:
Change a.Currency_Code to a.CurrencyCode
Reply
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
Reply
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
Reply
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!
Reply
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
Reply
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
Reply
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/
Reply
What is the datatype of @module?
Reply
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
Reply
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
Reply
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
Reply
Is this the full code are you using? It does not have any character like >
Reply
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'.
Reply
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
Reply
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.
Reply
Hi,
How can we include multiple case statement in single query
Reply
just simply separate the case statements with a comma(,)
Reply
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?
Reply
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..
Reply
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..
Reply
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)
Reply
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?
Reply
Copyright © 2011 - All Rights Reserved - Softron.in
Template by Softron Technology