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!

Case & Nested table selection and errors

hi

Here are the two tables again.

1)PATIENT(PATIENT_ID,NAME,CITY)

2) DISEASES(DISEASE_ID,NAME)

I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.

Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.

i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.

please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.

your help and insight is highly appreciated.

regards

Raju

For nested table modeling, you need a one to many relationship between the patient table and the disease table. Such a relationship would relate, for instance, a patient with the diseases he suffers. Your table would typically look like this:

1) PATIENT (PATIENT_ID, NAME, CITY)

2) DISEASES (PATIENT_ID, DISEASE_ID)

In this set of tables, you can define the relationship linking Patient.Patient_ID to Diseases.Patient_ID (assuming that one or more diseases were reported for most of the patients)

In the set of tables you mentioned above, assuming that Patient.Name is the patient's name and Diseases.Name is the name of the disease, it is not possible to have such a relationship between the tables. However, in most normalized DB architectures, your tables suggest that there must be another table, linking patients to diseases. That one should be modeled as nested.

Once you get this problem solved, it is possible during mining to use the actual disease name ( by setting the Name binding for the nested Disease_id column). If you have troubles setting this binding, please post a question here

Regards

|||

Thank you!

Sorry! i didn't mention about third table patient_diseases(patient_id, Disease_id). There is thrid table with foreign keys table for patient and diseases tables.

what table should i select as case and what table as nested? After that what colums should i select from which tables as Key, input, predict? could you let me setting the Name binding for the nested Disease_id column?

Your help is appreciated.

regards

raju

|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690139&SiteID=1&mode=1