Re[2]: Help with SQL

[Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG>]

     Thanks Frank

     Why did you add ps_job to the sql-plus code?

     Bill


______________________________ Reply Separator _________________________________
Subject: Re: Help with SQL
Author:  SQR-USERS@USA.NET at INTERNET
Date:    1/6/98 12:06 PM


     Bill, try this SQL-PLUS code:

     select
     c.deptid,
     q.union_cd,
     b.pay_end_dt,
     b.erncd,
     sum(b.oth_hrs),
     sum(b.oth_pay)
     from ps_pay_oth_earns b,
          ps_employment    q,
          ps_job           j,
          ps_pay_earnings  c
     where b.pay_end_dt between '01-JAN-97' and '31-DEC-97'
     and b.company    = c.company
     and b.paygroup   = c.paygroup
     and b.pay_end_dt = c.pay_end_dt
     and b.off_cycle  = c.off_cycle
     and b.page#      = c.page#
     and b.line#      = c.line#
     and b.addl#      = c.addl#
     and q.emplid     = c.emplid
     and q.empl_rcd#  = c.empl_rcd#
     and j.emplid     = q.emplid
     and j.empl_rcd#  = j.empl_rcd#
     and j.EFFDT      = (SELECT MAX(JJ.EFFDT)
                           from PS_JOB JJ
                          where JJ.EMPLID     = J.EMPLID
                            AND JJ.EMPL_RCD#  = J.EMPL_RCD#
                            AND JJ.EFFDT     <= b.pay_end_dt)
     and j.EFFSEQ     = (SELECT MAX(JJJ.EFFSEQ)
                           from PS_JOB JJJ
                          where JJJ.EMPLID     = J.EMPLID
                            AND JJJ.EMPL_RCD#  = J.EMPL_RCD#
                            AND JJJ.EFFDT      = J.EFFDT)
     group by c.deptid,
              q.union_cd,
              b.pay_end_dt,
              b.Erncd
     /

     gave me 55 rows in less than 30 seconds in test environment

     Good Luck
     Frank
     ______________________________ Reply Separator
     _________________________________
     Subject: Help with SQL
     Author:  SQR-USERS@USA.NET at MEMCPSMTP
     Date:    1/5/98 1:54 PM


     I am running the following sql within an sqr and it runs, and runs and
     runs.  Anybody (familiar with PeopleSoft) have any ideas?


     select
     c.deptid,
     q.union_cd,
     b.pay_end_dt,
     b.erncd
     sum(b.oth_hrs),
     sum(b.oth_pay)
     from ps_pay_oth_earns b, ps_employment q, ps_pay_earnings c where
     b.pay_end_dt between '01-JAN-97' and '31-DEC-97'
     and b.company = c.company
     and b.paygroup = c.paygroup
     and b.pay_end_dt = c.pay_end_dt
     and b.page# = c.page#
     and b.line# = c.line#
     and b.addl# = c.addl#
     group by c.deptid,
     q.union_cd,
     b.pay_end_dt,
     b.Erncd

     Thanks in advance

     Bill Bowers
     St. Peters Hospital
     Olympia, Washington
     (360) 393-5437