February 7, 2008 at 1:50 pm
This is what i got now:
select p.first_name
,p.last_name
,p.people_code_id
,case when td.event_id='sd 100' then (case transcript_seq when '002' then
(select final_grade from people as p
inner join transcriptdetail as td2
on p.people_code_id=td2.people_code_id
and td2.academic_year='2007'
and td2.academic_term='spring'
and td2.transcript_seq='002'
and td2.final_grade<>'w')
when '001'then
(select final_grade from people as p
inner join transcriptdetail as td3
on p.people_code_id=td3.people_code_id
and td3.academic_year='2007'
and td3.academic_term='spring'
and td3.transcript_seq='001'
and td3.final_grade<>'w')
else null end) end as 'sd 100'
from people as p
inner join transcriptdetail as td
on p.people_code_id=td.people_code_id
It retrieves rows, then immeditalty I get this error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Once i get this resolved. I need to basically need to create a case statment for every possible class correct?
February 7, 2008 at 2:03 pm
You are getting that error because the sub queries in your SELECT are returning more than one value. SQL Server will not allow obviously not allow this for good reasons. This looks like it could be written much simpler. You may be able to re-write the subqueries to return a single value, but this looks to be the makings of a poor performer. Can you post your table DDL and a few rows of sample data from each table?
February 7, 2008 at 2:10 pm
Peopel table-- primary key is people_code_id
The transcriptdetail table's primary keys are people_code_id, academic_year, academic_term, academic_session, event_id, event_sub_type, and section.
Id like to see a single row per student displaying EVERY class i include, whether that student took it or not, as well as their final_grade being pulled from their transcript seq of 002 if they have it, if not, then their transcript_seq of 001.
Sample
first name----last name----people code id----sd 100----sd 102----sd 103
john----------doe--------0000001512----------4.0------null--------3.7
jane----------smith-------0000000045---------3.7------null-------nulll
So in the above examples john doe completed class sd 100 with a 4.0 grade and sd 103 with a 3.7. He didnt take sd 102.
This make sense?
February 7, 2008 at 4:10 pm
First, review this item about the best way to post data and pose questions on the forums.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Then... you need to limit the subqueries to the class you are looking for (eventid = ????) so you only get one result back. But really there is probably a much better way to do it though without clear definition of tables and data it's hard to do.
Toni
February 7, 2008 at 4:19 pm
Limiting the subqueries on event_id won't help. The problem with the subqueries is that they return results for all people_code_ids. The subqueries need to be correlated back to the main query.
This is a pivot operation. Look up pivot or cross tab queries here on SSC and you'll find a ton of results. I'm leaving the office now so I don't have time to get to this today.
February 7, 2008 at 6:40 pm
Craig. Here is an alternate approach using a temporary table to assemble the result set. I was having trouble reconciling the denormalized results (grade for each class and possible different for transcript_sequence). This did work with the test data included.
declare @people table (ln varchar(10), fn varchar(10), code int primary key)
declare @trans table (yr varchar(4), term varchar(6), seq char(3),
grade varchar(5), event_id char(3), code int)
insert into @people -- populate test people table
select 'Ln1','Fn1',1 union all
select 'Ln2','Fn2',2
insert into @trans -- populate test transcript table
select 'yr1','T1','001', '1.0', 'CL1', 1 union all
select 'yr1','T1','001', '2.0', 'CL2', 1 union all
select 'yr1','T1','001', '3.0', 'CL3', 1 union all
select 'yr1','T1','001', '4.0', 'CL1', 2 union all
select 'yr1','T1','002', '3.5', 'CL1', 2 union all
select 'yr1','T1','002', 'w', 'CL2', 2
--- this is a temporary table to assemble the results in --
declare @out table (ln varchar(10), fn varchar(10), code int primary key,
yr varchar(4), term varchar(6), seq char(3),
CL1 varchar(5), CL2 varchar(5), CL3 varchar(5))
insert into @out (ln,fn,code) -- seed with info from people table
select ln, fn, code from @people
--- do grades for seq = '001'
update @out -- grades for CL1
set yr=t.yr, term=t.term, CL1 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL1' and t.seq='001' and t.grade<>'w'
update @out -- grade for CL2
set yr=t.yr, term=t.term, CL2 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL2' and t.seq='001' and t.grade<>'w'
update @out -- Grade for CL3
set yr=t.yr, term=t.term, CL3 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL3' and t.seq='001' and t.grade<>'w'
--- do the same to get the grades for seq = '002' - they will overwrite those of seq='001'
update @out
set yr=t.yr, term=t.term, CL1 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL1' and t.seq='002' and t.grade<>'w'
update @out
set yr=t.yr, term=t.term, CL2 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL2' and t.seq='002' and t.grade<>'w'
update @out
set yr=t.yr, term=t.term, CL3 = t.grade, seq=t.seq
from @trans t
join @out o
on t.code = o.code
where t.yr='yr1' and t.term='T1' and t.event_id = 'CL3' and t.seq='002' and t.grade<>'w'
-- pull the results from the temporary table
select fn
, ln
, code
, CL1, CL2, CL3
from @out
I know there is a set-based way to get this done but do not have the time now to find it. Maybe someone else will post that.
Toni
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply