Case statement question

[Thant]

Greetings,
I have a location in one of my reports that I am converting from a number to text using a case statement. As you can see there is a lot of duplication in the code to get a listing of all the seperate locations. Is there a more effecient way to write this?

(
select left({tblCsstars.LOCATION},4)
case"0006": "Randolph"
case"0001": "CMC Main"
case"0002": "CIR"
case"0026": "CIR-Mt. Holly"
case"0003": "University"
case"0005": "Mercy"
case"0007": "Pineville"
case"0030": "CarolinaCare"
case"0010": "King's Mtn"
case"1005": "Union"
case"0015": "Levines NICN"
case"0050": "Lincoln"
case"2049": "CPN"
case"2070": "CPN"
case"3019": "CPN"
case"2240": "CPN"
case"2269": "CPN"
case"3022": "CPN"
case"2108": "CPN"
case"2001": "CPN"
case"2124": "CPN"
case"2005": "CPN"
case"2165": "CPN"
case"2028": "CPN"
case"2221": "CPN"
case"2026": "CPN"
case"2121": "CPN"
case"0000": "Pineville"
case"2225": "CPN"
case"2413": "CMC Main"
case"2171": "CPN"
case"2140": "CPN"
case"3020": "CPN"
case"0098": "Union Nursing Home"
default: "Other"
)+" "+
(
select mid({tblCsstars.LOCATION},5)
case"592000": "Myers Park"
case"446300": "Levines"
case"446800": "Levines"
case"405001": "Levines"
case"405002": "Levines"
case"4465800":"Levines"
case"446100": "Levines"
case"446101": "Levines"
case"446900": "Levines"
case"447200": "Levines"
case"450000": "Levines"
case"559500": "Levines"
case"545000": "Levines"
case"5415000": "Levines"
case"576500": "Levines"
case"629500": "Residency"
case"592100": "NorthPark"
case"592200": "Biddle Point"
case"631500": "Long Term Care"
case"593000": "MCP"
case"592400": "Northcross"
case"629350": "Matthews"
case"629400": "Morrocroft"
case"563500": "CR Rehab"
case"357500": "NorthPark"
)

any ideas would be appreciated.
Thanks
Thant

 

[Turkbear]

Hi,
If those translations are relatively permanent, you would be better off creating a table with the full tblCsstars.LOCATION number and its translation as the 2 fields- then you could use a simple link between that table and your tblCsstars table. Updating that Location table would handle new codes.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thant]

I have that information in a table already but the problem is that the location codes i was provided are references to specific units within one hospital. There are no fields for just the facility themselves.My large hospital has over 900 beds so there are a lot of units. Would there be an easy way to combine all units like "CMC" into on label

0000000000	Clearing
0000009998	0000009998
00007581100	CMC-P-CLINICAL DECISION UNIT (CDU)
0001	CAROLINAS MEDICAL CENTER
0001000001	CMC-MD RELATED
0001000008	CMC-4B POST PARTUM
0001000900	CMC-LCH-LOBBY/ATRIUM
0001009988	CMC-WOUND CARE CENTER
0001009998	CMC-UNKNOWN
0001009999	CMC-OTHER
0001010000	CMC-CLINIC/OTHER
00012338500	CMC-CLINIC/CARDIAC REHAB
0001315000	CMC-ADMINISTRATION
0001321000	CMC-NURSING OFFICE
0001321900	CMC-ADMITTING
0001327500	CMC-ANES-MD

this is only a few of the 1140 codes that i am utilizing.

Any ideas would be great.
Thanks
Thant

 

[lbass]

You could simplify by sorting the codes in Excel or something and then identifying ranges that you could incorporate into the case statements, as in:

case "2001" to "2269", "3019" to "3022" : "CPN"

-LB