Adding a nested case statement

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 2005 Forums

Transact-SQL (2005)

Adding a nested case statement

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2009-10-19 : 11:57:54

Good morning!

I have the following SQL query in Excel, which works:


SELECT CASE LEFT(POSITIONS.CODE,2) WHEN 'OM' THEN 'OM1' ELSE LEFT(POSITIONS.CODE,2) END AS 'JobClass', CAST(RIGHT(POSITIONS.CODE,4) AS INT) AS 'JobCode'
FROM payroll.dbo.POSITIONS POSITIONS

I'm trying to figure out how to write a nested CASE statement so when CASE LEFT(POSITIONS.CODE,2) WHEN 'OM' is true, it will then evaluate CAST(RIGHT(POSITIONS.CODE,4) AS INT) AS 'JobCode' and return a JobClass of 'OM1' if the result is between 0 and 3021 and 'OM2' is > 3022.

Sample Data:
OM1111
OM9662
PR1122
TE2233

Expected results:

JobClass jobCode
OM1 1111
OM2 9662
PR 1122
TE 2233

I've tried both a second CASE expression and an IF-THEN-ELSE expression after the THEN, but I keep getting a syntax error about it not expecting a '(' after the SELECT.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-19 : 14:23:23

Something like:

SELECT 
	CASE 
		WHEN LEFT(POSITIONS.CODE,2)  = 'OM' 
			THEN 
				CASE
					WHEN CAST(RIGHT(POSITIONS.CODE,4) AS INT) > 3022 THEN 'OM2'
					ELSE 'OM1' 
				END
		ELSE LEFT(POSITIONS.CODE,2) 
	END AS 'JobClass', 
	CAST(RIGHT(POSITIONS.CODE,4) AS INT) AS 'JobCode'
FROM 
	payroll.dbo.POSITIONS POSITIONS

SBLatta
Starting Member

33 Posts

Posted - 2009-10-19 : 14:48:08

quote:

Originally posted by Lamprey

Something like:

SELECT 
	CASE 
		WHEN LEFT(POSITIONS.CODE,2)  = 'OM' 
			THEN 
				CASE
					WHEN CAST(RIGHT(POSITIONS.CODE,4) AS INT) > 3022 THEN 'OM2'
					ELSE 'OM1' 
				END
		ELSE LEFT(POSITIONS.CODE,2) 
	END AS 'JobClass', 
	CAST(RIGHT(POSITIONS.CODE,4) AS INT) AS 'JobCode'
FROM 
	payroll.dbo.POSITIONS POSITIONS

Worked like a charm. I've been trying all morning using <= 3021 and getting an error. That seems to have solved it!

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources