Case expressions may only be nested to level 10 - SQL Server

Ars_Maxer

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.

Top

Re: Case expressions may only be nested to level 10

Manivannan.D.Sekaran

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.

Top

Re: Case expressions may only be nested to level 10

phdiwakar

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

Top

Re: Case expressions may only be nested to level 10

DaleJ

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

select coalesce(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')

)

Top

Re: Case expressions may only be nested to level 10

Ars_Maxer

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!

Top