Limits of a Switch Function? (Recoding Value into Value Labels)

[blobb]

Hi. I am attempting to use a switch function in a query and i have come
across the error "Expression too complex in query experssion 'SWITCH(..."
Basically what I am attempting to do is recode the data for a report from the
original data (range of values 0 to 22) to the value labels for those values.

For example:

0 = No/Multiple Selection
1 = XX1
2 = XXX1
3 = XX2
4 = XXX2
etc...

Here my code (removed the numbers higher than 4 becuase I think you will get
the idea):

SELECT COUNT(Visit.PTid) AS [Count],
SWITCH(
PARTITION(Diagnosis,1,22,1) = ": 0","No/Multiple Selection",
PARTITION(Diagnosis,1,22,1) = "1:1","XX1",
PARTITION(Diagnosis,1,22,1) = "2:2","XXX1",
PARTITION(Diagnosis,1,22,1) = "3:3","XX2",
PARTITION(Diagnosis,1,22,1) = "4:4","XXX2",....) AS Grp,
PARTITION([Diagnosis],1,22,1) AS SortOrder
FROM qrySelectDemosAndVisit
GROUP BY Diagnosis;


Is there an easier way to convert the values (0 to 22) into the value labels
(ie., "No/Multiple Selection")?

Thanks in advance!

 

[Douglas J. Steele]

Is there an easier way to convert the values (0 to 22) into the value
labels
(ie., "No/Multiple Selection")?

You could create a table that maps from the partition to the label, and join
to it in your query.

 

[blobb]

thanks that worked for this field, but i have many fields that are coded this
way and need to have the switch function (or conditional IIFs) to express the
data in a meaningful way. I have looked all over to see if a switch
statement has a maximum number of expressions to be evaluated -- all with no
luck. Do you (or anybody) have any idea if there is a maximum number?

thanks again for your help.

 

[Douglas J. Steele]

I can't find them specified anywhere, but I'm sure there is a limit.

What about writing a VBA function to do it for you, using the SELECT CASE
inside the function?