Re: Help with SQL

[Robert Goshko <rgoshko@IBM.NET>]

Form the looks of it Bill you have a Cartesian join going on, you forgot to join the employment table with the pay earnings table (you are joining every row in the employment table with every combination of pay earnings/pay other earnings), you also missed a key field between pay earnings and pay other earnings (off_cycle)

Try this:

     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.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#
      group by c.deptid,
                q.union_cd,
                b.pay_end_dt,
                b.Erncd

Later...
      Rob

-----------------------------------------------------------
    Through the NIC... Over the twisted pair... 
       Off the Router... >>SWISH<<... Nothing but NET!
-------------------+-------------------+-------------------
   Robert Goshko   |  rgoshko@ibm.net  |  (403) 417-AXIS
 Axis Developments |                   | Sherwood Park, AB

-----Original Message-----
From:   Bill Bowers [SMTP:bowersb@PSPH.PROVIDENCE.ORG]
Sent:   Monday, January 05, 1998 2:55 PM
To:     Multiple recipients of list SQR-USERS
Subject:        Help with SQL

     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

Unrecognized Data: application/ms-tnef