Case expressions may only be nested to level 10

I'm running into this issue when I try to query a table (well a view) that was poorly designed and has many fields in the table as YesNo fields instead of a single field that just contains a value. (Fields A-N in the table for example)

So my query looks like:

CASE
WHEN A = 1 THEN 'A'
WHEN B = 1 THEN 'B'
WHEN C = 1 THEN 'C'
WHEN D = 1 THEN 'D'
WHEN E = 1 THEN 'E'
WHEN F = 1 THEN 'F'
WHEN G = 1 THEN 'G'
WHEN H = 1 THEN 'H'
WHEN I = 1 THEN 'I'
WHEN J = 1 THEN 'J'
WHEN K = 1 THEN 'K'
WHEN L = 1 THEN 'L'
WHEN M = 1 THEN 'M'
WHEN N = 1 THEN 'N'
END AS GeneratedField

Is there some other way I can approach this? I can't change the table as it isn't mine, but I need to run a report that looks at those fields and in GeneratedField places the result for the field that was checked.

Thanks.

Yes. Case Expression can be nested upto 10 Level.

But your expression is not looking like a nested expression.

Can you post the exact expression you are using.. Is any one of these A-N columns hold the value as 1 and reset 0.

|||

Ars_maxer,

There is another way of doing it, it's complicated than simpe case statement, though. Use Unpivot.

Here it is.

Code Snippet

declare @.tb table (
id int,
A int,
B int,
C int,
D int
);
insert into @.tb values(1, 1,0,0,0)
insert into @.tb values(2, 0,1,0,0)
insert into @.tb values(3,0,0,1,0)
insert into @.tb values(4,1,0,0,0)

select id, genvalue, vvalue from
( select id, A,B,C,D from @.tb)p
UNPIVOT
(vvalue for genvalue in (A,B,C,D) ) as unpvt
where vvalue =1

id genvalue vvalue
1 A 1
2 B 1
3 C 1
4 A 1

|||

This is a little hoakie, but here we go:

Code Snippet

declare @.a int, @.b int, @.c int, @.d int, @.e int

set @.a = 0

set @.b = 0

set @.c = 1

set @.d = 0

set @.e = 0

selectcoalesce(replace(convert(char(1),nullif(@.a,0)),'1','A'),

replace(convert(char(1),nullif(@.b,0)),'1','B'),

replace(convert(char(1),nullif(@.c,0)),'1','C'),

replace(convert(char(1),nullif(@.d,0)),'1','D'),

replace(convert(char(1),nullif(@.e,0)),'1','E')

)

|||

I'm not doing anything that isn't shown in my SQL statement there (Other than a SELECT and FROM and WHERE). However, nothing unusual.

Now if I run that SQL with say 8 statements then it works just fine.

I've read that this appears to be an issue and that others have run into it before. In some cases they wrapped the whole thing in a stored procedure on the linked server or their server and ran it that way.

In my case I'm executing this against a linked server and can't make a sproc there.

I'll play with the examples provided above and see if those will address the issue for me or not.

Thanks!