Use ARRAY for CASE statements

Question

Use ARRAY for CASE statements

cdun2

SSCertifiable

Points: 7893
More actions
March 30, 2004 at 3:21 pm

#108020

Hello,
I need to set up CASE statement(s) that need to evaluate the current value of a field called 'TeacherID', then give another field value as the result. Normally, I would hard code the value of the expression I am looking for, but it won't work in this situation. The following query is what I have now:
*******************
Select
Location2,
Grade,
Domain,
Strand,
StanNum,
StanDesc,
Case When TeacherID = 10371 Then SPercentage End as T1,
Case When TeacherID = 121371 Then SPercentage End as T2,
Case When TeacherID = 94371 Then SPercentage End as T3,
StanID
From tblMMrptStanResultsGrade
Where
Location2 = 371
and
Grade = 2
***********************
As you can see, I have specific values for TeacherID. However, content and number of TeacherID values are going to vary between Location2 and Grade. The maximum number of TeacherID values that can occur is 14.
My thought is that the TeacherID values need to be put into an array, then the CASE statements need to evaluate the index values (by index number) of the array. This query needs to accomodate a GUI report that will have a fixed number of T(x) fields on it, T1..T14. I imagine that if an array were used, a fixed number of indexes (0-13) would be set up, then the CASE statements would evaluate each index value.
I supposed that if an index were NULL, then the aliased field value for the particular CASE statement would just return NULL (?).
If an Array would be best to use in this situation, how would I set it up? Is there another way I should consider doing this?
Thank you again for your help!
CSDunn

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

There are no arrays in TSQL, however since the value that is returned is from a field and based on anothre field value, I'd suspect that a join is what you want not a CASE statement. Without having the schema and data, it's hard to say. Can you clarify with the schema posted and examples of the mapping?

noeld SSC Guru Points: 96590 More actions · Answer 2

How about a Lookup Table

Location2, Grade, TeacherID, SPercentage

And join that to tblMMrptStanResultsGrade

* Noel

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

Thanks for the suggestion. Do you have an example of this? I have not done Lookup Tables in SQL Server 2000.

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

I will present more detail on this issue shortly. Thanks!

CSDunn

David Burrows SSC Guru Points: 65142 More actions · Answer 5

How about this

CREATE TABLE tblLocationGradeTeacher (Location2 int, Grade int, T_id int, TeacherID int)

INSERT INTO tblLocationGradeTeacher VALUES (371, 2, 1, 10371)

INSERT INTO tblLocationGradeTeacher VALUES (371, 2, 2, 121371)

INSERT INTO tblLocationGradeTeacher VALUES (371, 2, 3, 94371)

SELECT

r.Location2,

r.Grade,

r.Domain,

r.Strand,

r.StanNum,

r.StanDesc,

MAX(CASE WHEN t.T_id = 1 THEN SPercentage ELSE 0 END) AS T1,

MAX(CASE WHEN t.T_id = 2 THEN SPercentage ELSE 0 END) AS T2,

MAX(CASE WHEN t.T_id = 3 THEN SPercentage ELSE 0 END) AS T3,

...

MAX(CASE WHEN t.T_id = 14 THEN SPercentage ELSE 0 END) AS T14,

r.StanID

FROM tblMMrptStanResultsGrade r

INNER JOIN tblLocationGradeTeacher t

ON t.Location2 = r.Location2

AND t.Grade = r.Grade

AND t.TeacherID = r.TeacherID

WHERE r.Location2 = 371

AND r.Grade = 2

GROUP BY r.Location2,

r.Grade,

r.Domain,

r.Strand,

r.StanNum,

r.StanDesc,

r.StanID

Far away is close at hand in the images of elsewhere.
Anon.

cdun2 SSCertifiable Points: 7893 More actions · Answer 6

Thanks for this idea. With this idea, I would need to set up MAX(CASE.. for 662 teachers in order to satisfy any combination of the Location2 and Grade parameters. At first glance, I think I could actually just add an IDENTITY column to the teacher_data_main table to make this work, and not have to add a new table.

Teachers are added to teacher_data_main, but never deleted. In the case that a new teacher was added (which happens automatically through a job), I would need to edit my code. Maybe not the ideal situation, but it would be easy enough to do.

I intend to post a more detail message on this situation as soon as I can get the message together.

CSDunn

David Burrows SSC Guru Points: 65142 More actions · Answer 7

I would need to set up MAX(CASE.. for 662 teachers in order to satisfy any combination of the Location2 and Grade parameters.

No, I assumed from your first post that there was a maximum of 14 teachers per Location/Grade. The table tblLocationGradeTeacher contains T_id which is the numerical position (1-14) for the TeacherID within the Location/Grade.

teacher_data_main was never mentioned in any previous post!!

number of TeacherID values are going to vary between Location2 and Grade

Does this mean that a Teacher (TeacherID) can move from one Location/Grade to another?

I think I could actually just add an IDENTITY column to the teacher_data_main table

If so, how are you going to translate the identity column (which may not be sequential in the selected data) to sequential numbers 1-14 for your case statements?

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

After reading your other post on this matter, I would do the following

create temp table containing identity column and teacherid. Insert the unique teacherid's (ordered if you wish) using the same where in your qyueary (location/grade). Join this table to the main table on teacherid and use the identity column in the case statements.

Far away is close at hand in the images of elsewhere.
Anon.

cdun2 SSCertifiable Points: 7893 More actions · Answer 9

David,

Thanks a ton for your help and patients, I really appreciate it!

CSDunn