a problem with case statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

a problem with case statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-27 : 08:12:11

Robert writes "Hello, I am trying to create a script that will pull college choices in order from a report, i.e., first_choice, second_choice, etc. However, the report lists the choices by school_code. I have a table that translates the school_code to school_name, but can not seem to get the case statement to work (if I am doing the right procedure). Here is the script so far as it pulls the school_codes:

Select C.alternate_id, C.student_ssn, C.Last_name, C.first_name, C.middle_init, A.year_in_school, A.data_valid, A.fed_school_1, A.fed_school_2, A.fed_school_3, A.fed_school_4, A.fed_school_5, A.fed_school_6
FROM say_fm_stu A, stu_award_year B, student C
WHERE (A.stu_award_year_token = B.stu_award_year_token) AND
(C.student_token = B.student_token) AND
(B.award_year_token = 2003 AND A.data_valid = 'Y' AND A.year_in_school = 1)
ORDER BY C.alternate_id,C.student_ssn,C.last_name,C.first_name,C.middle_init,A.year_in_school

However, instead of the A.fed_school_1 number, I need the D.school_name:

Select C.alternate_id, C.student_ssn, C.Last_name, C.first_name, C.middle_init, A.year_in_school, A.data_valid, d.school_name
FROM say_fm_stu A, stu_award_year B, student C, isir_school_codes D
WHERE (A.stu_award_year_token = B.stu_award_year_token) AND
(C.student_token = B.student_token) AND
(D.isir_school_code = A.fed_school_1) AND
(B.award_year_token = 2003 AND A.data_valid = 'Y' AND A.year_in_school = 1)
ORDER BY C.alternate_id,C.student_ssn,C.last_name,C.first_name,C.middle_init,A.year_in_school

but for all six choices, on same line if possible, as in the first script.

I am sure this can be done, but I don't seem to see the light for getting it done.
Thank you for your help.
Bob (SQL 6.5, WinNT with all svc packs)"

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 15:18:38

I think you need to join to isir_school_codes 6 times, once for each school choice.

Brett

8-)

PS I don't see a CASE Statement anywhere...

Edited by - x002548 on 05/27/2003 15:19:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources