#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Single row subquery errors during update


    I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.

    I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.

    PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).

    PHP Code:
    UPDATE PS_NTSR_GF_STUFILE a
     SET a
    .NTSR_GF_ENRL_STAT nvl((
                                
    SELECT b.GBSA_SUB1
                                FROM PS_GBSA_DTL b
    PS_ACAD_STDNG_ACTN c
                                WHERE c
    .ACAD_STNDNG_STAT b.GBSA_VALUE
                                
    AND b.GBSA_PRCS='NTSR_GDFT_AE'
                                
    AND b.GBSA_CRIT='ACAD_STANDING'
                                
    AND b.INSTITUTION 'DL773'
                                
    AND c.EFFDT = (
                                                
    SELECT MAX(f.EFFDT)
                                                
    FROM PS_ACAD_STDNG_ACTN f
                                                WHERE a
    .EMPLID f.EMPLID
                                                
    AND a.ACAD_CAREER f.ACAD_CAREER
                                                
    AND f.EFFDT <= SYSDATE)
                                AND 
    c.STRM = (
                                                
    SELECT MAX(d.STRM)
                                                
    FROM PS_ACAD_STDNG_ACTN d
                                                WHERE a
    .EMPLID d.EMPLID
                                                
    AND a.ACAD_CAREER d.ACAD_CAREER)
                                AND 
    c.EFFSEQ = (
                                                
    SELECT MAX(e.EFFSEQ)
                                                
    FROM PS_ACAD_STDNG_ACTN e
                                                WHERE a
    .EMPLID e.EMPLID
                                                
    AND a.ACAD_CAREER e.ACAD_CAREER)
                                AND 
    c.INSTITUTION = (
                                                    
    SELECT GBSA_VALUE
                                                    FROM PS_GBSA_DTL
                                                    WHERE INSTITUTION 
    'DL773'
                                                    
    AND GBSA_PRCS='NTSR_GDFT_AE'
                                                    
    AND GBSA_CRIT='DEFAULT_INSTITUTION')
                                ), 
    ' '); 
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Run the select on the data to see exactly why you getting multiple rows returned:
    Code:
    select * --b.gbsa_sub1
     from  ps_gbsa_dtl b join ps_acad_stdng_actn c on b.gbsa_value  =  c.acad_stndng_stat
    where  b.gbsa_prcs    =  'NTSR_GDFT_AE'
     and   b.gbsa_crit    =  'ACAD_STANDING'
     and   b.institution  =  'DL773'
     and   c.effdt  =  ( select max( f.effdt )
                          from  ps_acad_stdng_actn f
                         where  a.emplid       =  f.emplid
                          and   a.acad_career  =  f.acad_career
                          and   f.effdt       <=  sysdate )
     and   c.strm   =  ( select max( d.strm )
                          from  ps_acad_stdng_actn d
                         where  a.emplid       =  d.emplid
                          and   a.acad_career  =  d.acad_career )
     and   c.effseq =  ( select max( e.effseq )
                          from  ps_acad_stdng_actn e
                         where  a.emplid       =  e.emplid
                          and   a.acad_career  =  e.acad_career )
     and   c.institution = ( select gbsa_value
                              from  ps_gbsa_dtl
                             where  institution  =  'DL773'
                              and   gbsa_prcs    =  'NTSR_GDFT_AE'
                              and   gbsa_crit    =  'DEFAULT_INSTITUTION' )
    
    select gbsa_value
     from  ps_gbsa_dtl
    where  institution  =  'DL773'
     and   gbsa_prcs    =  'NTSR_GDFT_AE'
     and   gbsa_crit    =  'DEFAULT_INSTITUTION'

IMN logo majestic logo threadwatch logo seochat tools logo