Easy way to convert long "CASE" statement into a LOOKUP Table

Question

Easy way to convert long "CASE" statement into a LOOKUP Table

askquestions

Ten Centuries

Points: 1101
More actions
October 15, 2010 at 11:00 am

#97769

Hi guys,
Can you help pls. I've just "inherited" a very very long CASE statement (over 1000 whens / lines))!
A new code is released by each of the 5 departments every forthnight which needs to be mapped to the corrent department and they have just been adding to this case statement every time this happens.
I am now thinking of probably converting this case statement into a lookup table. Is this a good idea? and also what is the quickest and easiest way of doing this without copying/pasting each line?
Many thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Depending on the pattern of the When...Then statements, you might be able to use some basic string manipulation on it to turn it into an insert statement. But it would depend on the pattern.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 2

It definitely sounds like a good idea to move this into a table.

Can you post a few of the lines? This will help us see what might be the best way to do this.

Also, what version of SQL are you using?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

askquestions Ten Centuries Points: 1101 More actions · Answer 3

Many thanks guys. Below is an example: The whole case statement is actually contained inside a function that is called with the appropriate fieldname to get the department for that code.

CREATE function DeptCode_Lookup

(@LookupField varchar(255))

RETURNS varchar(255)

AS

BEGIN

DECLARE @Department varchar(255)

set @Department = case rtrim(@LookupField)

when 'THY&TFG-3ED' then 'Travel'

when 'J - WINTER 02' then 'Travel'

when 'X - Deals' then 'Travel'

when 'P - WALK 00' then 'Travel'

when '02 DEAL' then 'Travel'

when 'T ACTIVE' then 'Payments'

when 'MOUNTAINS' then 'Travel'

when 'Families TX' then 'Family'

when 'F - ACTIV 77' then 'Payments'

when '1998 Review' then 'Payments'

when 'WINTER FMX' then 'Payments'

when 'RSFM FAMILY' then 'Family'

when 'Fam - 98/99' then 'Family'

when 'COMP 2000' then 'Procurement'

when '99/00 Furnitures' then 'Procurement'

when 'TRAIN & TEACH' then 'Procurement'

when 'G.ALF' then 'Operations'

when 'D_A_G_99' then 'Operations'

when 'WEEKLY 1889' then 'Operations'

ELSE @LookupField end

RETURN @Department

END

The function is then used as part of a select statement:

--select dbo.DeptCode_Lookup(fieldname)

select dbo.DeptCode_Lookup(THY&TFG-3ED)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Copy-and-paste the When...Then statements into Management Studio.

Ctrl+H (Search-and-Replace) for "when", replace with "union all select".

Ctrl+H (Search-and-Replace) for "then", replace with ",".

Delete the first "union all", leaving the whole thing starting with "select".

Voila, you have a select statement that can be used to insert into a lookup table.

If the words "then" or "when" appear in any of the columns, you'll get an error message, and you can correct those as they come up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

askquestions Ten Centuries Points: 1101 More actions · Answer 5

PERFECT!.

Many thanks GSquared - just what i was looking for - saved me hours of typing!

thank you