Single row subquery errors during update

Dev Shed Forums Sponsor:

May 22nd, 2012, 03:06 PM

SFDonovan

Contributing User

Join Date: Mar 2008

Location: Texas

Posts: 350

Time spent in forums: 3 Days 14 h 45 m 42 sec
Reputation Power: 6

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')

                            ), ' ');

May 23rd, 2012, 05:25 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 191

Time spent in forums: 2 Days 9 h 55 m 30 sec
Reputation Power: 41

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'

Viewing: Dev Shed Forums > Databases > Oracle Development > Single row subquery errors during update

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump