Re: anomalies in a BEGIN-SELECT

[Kenneth Lawrence Gartman <osmium@EROLS.COM>]

Hi, Etienne!

    Not too much can go wrong with your procedure:
1.  The procedure isn't being called.  Do you see BEGIN-PROCESS MAIN displayed?

2.  The SELECT returns no rows.  Do you see "before do EVALUATE-DAYS"
displayed?
3.  Should you change the August-September dates to September-October?
4.  Should you be using dates with a four-digit year (29-AUG-1999)?
5.  Has the Payroll data for each employee been updated?  Maybe the process
failed.
6.  Has anyone fooled with your program?  Check the creation date of the
program in production.

                            Happy computing,
                            Ken



"Etienne Doré" wrote:

> Hi all!  I'm new on this group and I hope you will be able to help me!  My
> working environment is Oracle 7, PeopleSoft 6.01(now migrating to 7.54) and
> SQR 4.3.2 I've made a program which makes balanced pay, depending on the
> number of days worked by each employee in the 'B800' or 'B1000' groups, we
> give a 800$ or 1000$ advance or we take back the advance.  The following
> code was working well and 2 weeks later, it doesn't execute the display 'do
> EVALUATE-DAYS' and the commands after.
>
> begin-procedure begin-process
>
> display 'BEGIN-PROCESS MAIN'
>
> BEGIN-SELECT
> sum(D.est_gross) &gross_sum
> P.name
> E.EMPLID
> E.EFFDT
> E.TIME_RPTG_STATUS
> G.GROUP_ID
> D.ACCT_CD
> D.FB_TL_ACTIVITY
> D.FB_TL_COST
>
>   display 'before do EVALUATE-DAYS'
>   do EVALUATE-DAYS
>   let $group = &G.GROUP_ID
>   let $emplid = &E.EMPLID
>   let $ename = &P.NAME
>   display 'Number of days'
>   display &nb_days
>   if (&nb_days < 9) or (&nb_days >= 12)
>         do PROCESS-BAL
>   end-if
>
> FROM PS_TL_EMPL_DATA E,
> PS_TL_GROUP_DTL G,
> PS_PERSONAL_DATA P,
> PS_TL_EMPL_DLYTASK D
> WHERE E.EFFDT = (SELECT MAX(E1.EFFDT) FROM PS_TL_EMPL_DATA E1
>                 WHERE E1.EMPLID = E.EMPLID
>                  AND E1.EFFDT < SYSDATE)
> AND E.TIME_RPTG_STATUS = 'A'
> and E.EMPLID = G.EMPLID
> and E.EMPLID = P.EMPLID
> and E.EMPLID = D.EMPLID
> and (G.GROUP_ID = 'B800' or G.GROUP_ID = 'B1000')
> AND D.DATE_UNDER_RPT >= '29-AUG-99'
> and D.DATE_UNDER_RPT < '26-SEP-99'
> AND D.TIME_RPTG_CD = 'BAL'
> group by E.emplid, E.effdt, E.time_rptg_status, G.group_id, D.acct_cd,
> D.fb_tl_activity, D.fb_tl_cost, P.NAME
> order by E.emplid
> END-SELECT
>
> end-procedure begin-process
>
> Of course, I tested the select exactly as it is with SQL Worksheet and
> everything is fine.  I tried all my brain could imagine to bypass this
> illogical bug but nothing satisfies.  I need that bug fixed for next week,
> it's the pay!!!  aaargghhh!  HELP!!!!
>
> Thank you very much!
>
> Etienne Doré
> IT Technician
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com