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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Case statements

Author  Topic 

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:14:44
What should i use if the case statement is over the 10 nested level in the select statement?

thanks for any advise

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-09-29 : 15:21:41
what do you mean by that?? i don't understand your question.
can you provide an example?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:22:50
case when cc.prid=5000002 then 'SK' else case when cc.prid=5000003 then 'VA' else
case when cc.prid=5000006 then 'JD' else case when cc.prid=5000007 then 'BV' else
case when cc.prid=5000008 then 'SP' else case when cc.prid=5000015 then 'CL' else
case when cc.prid=5000032 then 'FM' else case when cc.prid=5000033 then 'NP' else
case when cc.prid=5000034 then 'ML' else case when cc.prid=5000035 then 'SB' else
case when cc.prid=5000036 then 'WC' else case when cc.prid=5000037 then 'PV' else
case when cc.prid=5000038 then 'MV' else case when cc.prid=5000049 then 'PA' else '-' end end end end end end end end end end end end end end HRInternalDecs,
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-09-29 : 15:24:20
ohhh...
emmm... why do you nest this anyway??
as i see it there's no need for that.
you always check cc.prid for a value and as far as i know one row can't have 2 different values at the same time.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:27:44
Hey spirit1,
well this works if the level is under 10, most of the time its maybe 4 levels if that... but i am not sure what to do if there is more than 10?
the results are for example...
49709 wongem Wong, Eddie .Vacation 8.000000 2005-09-06 00:00:00.000
49709 wongem Wong, Eddie .Vacation 8.000000 2005-09-07 00:00:00.000
999005 koenigmx Koenig, Marilyn .Vacation 6.000000 2005-09-09 00:00:00.000
999007 leedm Lee, Dorothy .Vacation 6.000000 2005-09-12 00:00:00.000
999007 leedm Lee, Dorothy .Vacation 6.000000 2005-09-14 00:00:00.000

I want vacation to say vacation but also in another column have the 'VA' listed.
thanks

Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-09-29 : 15:30:49
can you post the whole sql?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:31:42
sure here you go:
select
c.url EmpID,
r.unique_name RExID,
r.full_name Resource,
case when te.prAssignmentID IS NULL then cc.prName end Task,
s.slice hours,
s.slice_date,
tp.prstart
from niku.PRJ_BLB_SLICES s
INNER JOIN niku.PRJ_BLB_SLICEREQUESTS sr ON s.SLICE_REQUEST_ID=sr.ID
INNER JOIN niku.PRTimeEntry te ON te.prID = s.PRJ_OBJECT_ID
INNER JOIN niku.PRTimeSheet ts ON te.prTimeSheetID=ts.prID
INNER JOIN niku.SRM_RESOURCES r ON ts.prResourceID = r.ID
INNER JOIN niku.srm_contacts c ON r.id=c.principal_id
INNER JOIN niku.PRTimePeriod tp ON ts.prTimePeriodID=tp.prID
LEFT JOIN niku.PRChargeCode cc ON te.prChargeCodeID=cc.prID
LEFT OUTER JOIN niku.PRAssignment a ON te.prAssignmentID=a.prID
WHERE sr.FIELD = 5
and ts.prStatus<5 and
not exists (select 'x' from niku.prtimesheet xts where xts.pradjustedid is not null and xts.pradjustedid=ts.prid) and
s.slice_date BETWEEN '2005-09-01 00:00:00.000' AND '2005-09-30 00:00:00.000'
and cc.prid IN (5000002,5000003,5000006,5000007,5000008,5000015,5000032,5000033,5000034,5000035,5000036,5000037,5000038,5000049)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-09-29 : 15:34:22
so where's the nested case for cc.prid here???

Go with the flow & have fun! Else fight the flow
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:35:27
i took it out because you can't go over the 10 level...
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:38:49
select
c.url EmpID,
r.unique_name RExID,
r.full_name Resource,
case when te.prAssignmentID IS NULL then cc.prName end Task,
case when cc.prid=5000002 then 'SK' else case when cc.prid=5000003 then 'VA' else
case when cc.prid=5000006 then 'JD' else case when cc.prid=5000007 then 'BV' else
case when cc.prid=5000008 then 'SP' else case when cc.prid=5000015 then 'CL' else
case when cc.prid=5000032 then 'FM' else case when cc.prid=5000033 then 'NP' else '-' end end end end end end end end HRInternalDecs,
s.slice hours,
s.slice_date
from niku.PRJ_BLB_SLICES s
INNER JOIN niku.PRJ_BLB_SLICEREQUESTS sr ON s.SLICE_REQUEST_ID=sr.ID
INNER JOIN niku.PRTimeEntry te ON te.prID = s.PRJ_OBJECT_ID
INNER JOIN niku.PRTimeSheet ts ON te.prTimeSheetID=ts.prID
INNER JOIN niku.SRM_RESOURCES r ON ts.prResourceID = r.ID
INNER JOIN niku.srm_contacts c ON r.id=c.principal_id
INNER JOIN niku.PRTimePeriod tp ON ts.prTimePeriodID=tp.prID
LEFT JOIN niku.PRChargeCode cc ON te.prChargeCodeID=cc.prID
LEFT OUTER JOIN niku.PRAssignment a ON te.prAssignmentID=a.prID
WHERE sr.FIELD = 5
and ts.prStatus<5 and
not exists (select 'x' from niku.prtimesheet xts where xts.pradjustedid is not null and xts.pradjustedid=ts.prid) and
s.slice_date BETWEEN '2005-09-01 00:00:00.000' AND '2005-09-30 00:00:00.000'
and cc.prid IN (5000002,5000003,5000006,5000007,5000008,5000015,5000032,5000033,5000034,5000035,5000036,5000037,5000038,5000049)

here it is with 8 levels, then the result would be : I dont want any null columns.
id name task hrinternaldesc hours date
BAKERD Baker, David .Vacation VA 7.000000 2005-09-14 00:00:00.000
BAKERD Baker, David .Vacation VA 7.000000 2005-09-14 00:00:00.000
mooresm Moore, Sue .Vacation VA 4.000000 2005-09-12 00:00:00.000
mooresm Moore, Sue .Vacation VA 3.000000 2005-09-15 00:00:00.000
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-09-29 : 15:40:04
as i said, you don't have to nest this at all:

case 	when cc.prid=5000002 then 'SK' 
	when cc.prid=5000003 then 'VA' 
	when cc.prid=5000006 then 'JD' 
	when cc.prid=5000007 then 'BV' 
	when cc.prid=5000008 then 'SP' 
	when cc.prid=5000015 then 'CL' 
	when cc.prid=5000032 then 'FM' 
	when cc.prid=5000033 then 'NP' 
	when cc.prid=5000034 then 'ML' 
	when cc.prid=5000035 then 'SB' 
	when cc.prid=5000036 then 'WC' 
	when cc.prid=5000037 then 'PV' 
	when cc.prid=5000038 then 'MV' 
	when cc.prid=5000049 then 'PA' 
	else '-'
end HRInternalDecs,


and those when's can be as many as you like... ok there's a limit but it's not 10

Go with the flow & have fun! Else fight the flow
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:40:32
spirit1,
does that not make sense? What can i do to make it better?
thanks
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:41:45
cool i will give that a try, i am sorry i thought i had to have that else case in there, my bad, i am still green but getting better. thanks
Go to Top of Page

furrelkt
Starting Member

49 Posts
Posted - 2005-09-29 : 15:45:51
perfect, thanks i appreciate it. i am glad i have a forum that i can get help. its a long learning process. thanks

K
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site