Re: Help with SQL

[Steven Baldwin <Steven.Baldwin@HANCORP.COM.AU>]

I'm no peoplesoft expert, but it would appear you are not joining the
ps_employment table (alias q) with anything else, giving a cartesian
join against this table.  If it contains a lot of rows, and from the
name it sounds like it might, this could explain the unexpectedly long
execution time.

Hope this helps.

Steve
----------------------------------------------
Steve Baldwin

Phone          : +61 3 9840 3829
Fax            : +61 3 9840 3824
E-Mail         : Steven.Baldwin@hancorp.com.au
----------------------------------------------

> ----------
> From:         Bill Bowers[SMTP:bowersb@PSPH.PROVIDENCE.ORG]
> Sent:         Tuesday, 6 January 1998 8:54
> 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
>