Need help with Partially complete query.

  • 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?

  • 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?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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?

  • 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

  • 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.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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