Re: anomalies in a BEGIN-SELECT

["Peters, Brian" <PetersB@HSN.NET>]

Arturo,
As you may or may not know, SQR commands within a begin-select will not be
processed unless the  SQL returns a row.  Start by testing the SQL in a tool
like SQLPlus, or comment out each one of select criteria statements and run
the SQR.  Why have you hard coded dates?

Brian
PeopleSoft Implementation Team at HSN  

-----Original Message-----
From: Arturo Vega [mailto:arturo_vega-consultant@PEOPLESOFT.COM]
Sent: Tuesday, October 19, 1999 6:47 PM
To: Multiple recipients of list SQR-USERS
Subject: Re: anomalies in a BEGIN-SELECT


are your dates OK   (including SYSDATE)?

please check your DATE MASK in sqr.ini, I think there is the problem.

Additionally, run your SQR with flag -S   with this flag you'll see the
cursors and the status, check the select is returning at least 1 row,
otherwise it will never perform your   'do EVALUATE-DAYS' procedure

Hope This Helps

Arturo Vega
PeopleSoft Consultant
Mexico



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