Need help with complex 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

General SQL Server Forums

New to SQL Server Programming

Need help with complex CASE statement

Author

Topic

Jason2112
Starting Member

17 Posts

Posted - 2009-04-22 : 15:11:12

I have view with fields ItemNo and ItemClass. Multiple ItemNo's are assigned to any particular ItemClass. I'm working with a CASE statement that looks at the ItemClass and creates a new "user friendly" name. I need to create new names based on specific ItemNo's within the ItemClass. Here's what I have


CASE WHEN ItemClass = 9 THEN 'Cups'
     WHEN ItemClass = 11 THEN 'Plates'
     ELSE 'NA' END AS Description

What I need is something like this:


CASE WHEN ItemClass = 9 AND ItemNo = 1234 THEN 'Red Cups'
     WHEN ItemClass = 9 AND ItemNo = 2345 THEN 'Blue Cups'

and so on...

Is there a way to condense the code so that I don't have to write a new WHEN for each ItemNo within the ItemClass? I'm looking at 25+ ItemNo's within each of 5 different ItemClasses (that's a lot of code to write).

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 15:13:52

create a table
ItemClass, ItemNo, description

and left join to that

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jason2112
Starting Member

17 Posts

Posted - 2009-04-22 : 17:01:59

That's probably the easiest way but I'd rather not go down that path if I don't have to - requires a lengthy explanation on why we have a "rogue" table (SarbOx compliance).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 17:15:55

Make a on-the-fly derived table in your query to do what nr suggests.

SELECT s.*, COALESCE(d.Description, 'NA')
FROM SourceTable AS s
LEFT JOIN (
SELECT 9 AS ItemClass, 1234 AS ItemNo, 'Red Cups' AS Description UNION ALL
SELECT 9, 2345, 'Blue Cups'
) AS d ON d.ItemClass = s.ItemClass AND d.ItemNo = s.ItemNo
ORDER BY COALESCE(d.Description, 'NA')

E 12°55'05.63"
N 56°04'39.26"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-23 : 06:44:58

"Rogue" table? Why is it "rogue"? Because it helps you make your database comply with FNF?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

pootle_flump

1064 Posts

Posted - 2009-04-23 : 07:58:48

An old fave:
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

Jason2112
Starting Member

17 Posts

Posted - 2009-04-23 : 11:20:14

quote:
Originally posted by DonAtWork

"Rogue" table? Why is it "rogue"? Because it helps you make your database comply with FNF?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

... because it would be a UDT table, which requires a lengthy explanation on what it does, what it's used for, and why we can't use the current tables to achieve the same end result. I don't make the rules, I just avoid them.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-23 : 11:43:24

The new base table is easily the best solution no matter the meetings / change reports you'll have to file now -- it will save you problems later.

If you make a derived table as Peso suggestions then you'll have to do that again and again in future queries. I bet at some point down the line you are going to have more/less entries in your derived table in one query than in another.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pootle_flump

1064 Posts

Posted - 2009-04-23 : 11:47:33

Is 1234 always red and 2345 always blue? And vice versa (is red always 1234 and blue always 2345)?

pootle_flump

1064 Posts

Posted - 2009-04-23 : 11:57:48

If so then:


.....
INNER JOIN
	(
		SELECT	*
		FROM	(
				SELECT 	ItemClass 	= 9
					, ItemName	= 'Cups'
				UNION ALL
				SELECT 	ItemClass 	= 11
					, ItemName	= 'Plates
			) AS items
		CROSS JOIN
		FROM	(
				SELECT 	ItemNo		= 1234
					, ItemColour	= 'Red'
				UNION ALL
				SELECT 	ItemNo	= 2345
					, ItemColour	= 'Blue'
			) AS colours
	) AS ItemColours
ON	....

Jason2112
Starting Member

17 Posts

Posted - 2009-04-23 : 20:07:31

Thanks for all the help. I went ahead and built the conversion table and with a simple case statement was able to get the results I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources