case when - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case when

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-11 : 21:20:13

Hi all. what is wrong with my query?

DECLARE @empDcno varchar(20), @secnum int
set @empDcno = '205'
set @secnum = 1

select l.* , e.paytype
from hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join hrsetbenefitsleave as b on l.type = b.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
where e.sectioncode =
case @secnum
when 1 then '1001'
when 2 then '1002'
else ''
end

did i use the case/when statement properly? if not, how could i query this in correct format?

Edit .:. this is my changes. still not working...

Edit .:. Ok got it...thanks. @secnum instead.

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 21:38:43

[code]
WHERE e.sectioncode =
case secnum
when 1 then ~~where e.sectioncode =~~ '1001'
when 2 then ~~where e.sectioncode =~~ '1002'
end
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 21:40:40

Why don't you refer to the Books OnLine for the correct syntax ?

quote:

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-11 : 22:49:04

Hi again. I tried all my best to come up with the correct output, but all my trials failed. I would like to ask help again. thanks.

DECLARE @empDcno varchar(20), @secnum int, @allsections varchar(max)
set @empDcno = '205,14'
set @secnum = 4
set @allsections = '1001, 1002, 1003'

select l.* , e.paytype, e.sectioncode
from hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join hrsetbenefitsleave as b on l.type = b.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
where e.sectioncode =
case @secnum
when 1 then '1001'
when 2 then '1002'
when 3 then '1003'
else
end

In Else (If secnum == 4) i want to use it the CSVTable, wherein where e.sectioncode = @allsections.

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 23:06:17

you mean when @secnum = 4, you want a where condition like this ?


e.sectioncode in ('1001', '1002', '1003')

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-11 : 23:15:43

yes, with the use of CSVTable if possible.

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 23:22:53

Then why not standardize and use CSVTable instead of case when end with @secnum


select  l.* , e.paytype, e.sectioncode
from 	hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join hrsetbenefitsleave as b on l.type = b.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
inner join CSVTable(@allsections) as sc on e.sectioncode = sc.stringval

assign '1001' to @allsections when @secnum is 1
assign '1002' to @allsections when @secnum is 2 etc
and assign '1001,1002,1003' to @allsections when @secnum is 4

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-11 : 23:35:09

Damn. im dumbass.lol. I made my query so complicated.

I thought i can't inner join 2 CSVTable. Oh well i got it....
my front end passes set of sections to the variable @getallsections.

DECLARE @empDcno varchar(20), @secnum int, @allsections varchar(max)
set @empDcno = '205,14'
~~set @secnum = 1~~
set @allsections = @getallsections

select l.* , e.paytype, e.sectioncode
from hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join hrsetbenefitsleave as b on l.type = b.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
inner join CSVTable(@allsections) as sc on e.sectioncode = sc.stringval

Thanks for the guidance KH.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-11 : 23:36:59

then you can remove the @secnum from your SP.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-14 : 21:42:59

Hi.
Is it possible to make a CASE/WHEN function in JOINS?

This is my query:

DECLARE @count1 int, @empDcno varchar(10)
set @empDcno = '25,100'
set @count1 = 0

select l.* , e.paytype, e.sectioncode, e.empno,dc.fullname,e.hrswork,
earnedDays = lc.earned, availedDays = lc.availed,
earnedHours = (lc.earned * e.hrswork), availedHours = (lc.availed * e.hrswork),
balHours = (lc.earned * e.hrswork) - (lc.availed * e.hrswork),
balDays = (lc.earned - lc.availed)

from hrempleaveledger as l
inner join hremployees as e on l.empdcno = e.empdcno
inner join psdatacenter as dc on dc.dcno = l.empdcno
inner join hrempleavecredits as lc on lc.empdcno = e.empdcno
inner join hrsetbenefitsleave as ben on l.type = ben.type
inner join mscsections as s on e.sectioncode = s.sectioncode
inner join mscdivisions as d on e.divisioncode = d.divisioncode
inner join mscbranches as br on e.branchcode = br.branchcode
inner join mscwarehouse as w on e.whcode = w.whcode

case count1
when 1 then inner join CSVTable(@empDcno) as R on e.empdcno = R.stringval
else 0
end

this is the error:
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'case'.

Thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 21:46:10

No. You can't

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-14 : 21:51:17

thanks khtan. if not possible, can you give me suggestion or any suggestions to make a DYNAMIC JOIN...

thanks.
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 21:56:16

you can consider using IF .. ELSE block

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-14 : 22:00:02

You said before that it is not also possible to use IF..ELSE in a JOINS... if im not mistaken...
I think its not possible coz as what you have said before you can't either use IF..ELSE inside a query.

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 22:07:09

not in joins.


IF @count1 = 0
begin
    <query here with join to CSVTable(@empDcno)>
end
else
begin
   <query here without joining to CSVTable(@empDcno)>
end

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-14 : 22:13:27

That is what i did before. I just want to simplify my query. If there is no other alternative, i think i should stick with that.

Thanks.
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-19 : 05:19:59

Hi all. I have difficulty on the CASE function.

DECLARE @isResigned bit
SET @isResigned = @getisResigned

select e.* from hremployees
inner join psdatacenter d on e.empdcno = d.dcno
where e.dateresign is null
AND
e.dateresign is
case @isResigned
when 1 then not null
else null
end

This is the query i want to get up but it has error. @isResigned may have a value of 0 or 1. If 0, it will display all employees that still working in the company. If 1, display all employees working in the company AND those who resigned.

thanks.
-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 05:43:54

[code]DECLARE @isResigned bit
SET @isResigned = @getisResigned

select e.*
from hremployees
inner join psdatacenter as d on e.empdcno = d.dcno
where CASE
WHEN @IsResigned = 1 AND e.DateResigned IS NOT NULL THEN 1
WHEN e.DateResigned IS NULL THEN 1
ELSE 0
END = 1[/code]

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-19 : 05:52:29

o thanks Peso. **Bow down**

-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 06:02:42

Also try this

select		e.*
from		hremployees 
inner join	psdatacenter as d on e.empdcno = d.dcno
where		e.DateResigned IS NULL
union all
select		e.*
from		hremployees 
inner join	psdatacenter as d on e.empdcno = d.dcno
where		@IsResigned = 1
		and e.DateResigned IS not NULL

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-19 : 06:11:30

*scratching head* I can't understand your first query, but great it works well. in your 2nd query i understand it but i refer to use the first 1.

-Ron-

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources