Re: ? Last Record - Control Break Processing

[Tony DeLia <tdelia@EROLS.COM>]

Phil,

   Eric is correct in that you don't need to know how may records are in
a SELECT (Or in a flat-file for that matter). This is known as basic
Control-Break processing... You test breaks from your MAJOR key down to
your MINOR key. When a break occurs your particular Break routine must
process all minor break totals before proceeding with its' own totals.
Once totals have been printed they must be accumulated forward (into a
higher level key if exists) then cleared. One thing you have to remember
is don't process on the first break - I usually set my keys to '$' so I
know to bypass totals on the first pass. And as Eric pointed out - And
this is crucial - Always remember your final total break after the
end-select! The final total break should call all lower level breaks
so it can produce all totals properly. Below is the structure of Manual
Control Break Processing... SQR also provides ON-BREAK commands where
they handle the maintenance of your Break Keys - I don't particularly
care for them though.

! Manual Control Break Processing....

..
..

do Init-Totals

begin-select

substr(e.deptid,1,2)                            &e.region
e.deptid
e.jobcode
e.emplid
o.erncd
e.hourly_rt
..
..
p.name
o.oth_hrs
o.oth_earns
j.wj_natural_glacct
j.descr

     if $r-key <> &e.region
        do Region-Totals
     else
        if $d-key <> &e.deptid
           do Deptid-Totals
        else
           if $j-key <> &e.jobcode
              do Jobcode-Totals
           else
              if $e-key <> &e.emplid
                 do Emplid-Totals
              else

                 if $o-key  <> &o.erncd
                 or #o-key2 <> &e.hourly_rt
                    do Erncd-Totals
                 end-if
              end-if
           end-if
        end-if
     end-if

     do Accum-Totals

 from ps_pay_earnings     e,
      ps_pay_oth_earns    o,
      ps_personal_data    p,
      ps_jobcode_tbl      j
where ........
order by e.deptid            asc,
         e.jobcode           asc,
         e.emplid            asc,
         o.erncd             asc,
         e.hourly_rt         asc,
         e.pay_end_dt        asc

end-select

do Final-Totals
..
..
..

! Sample Break (All should have same structure)

begin-procedure Deptid-Totals

do Jobcode-Totals

if $d-key <> '$'

   print #d-tot        (  0, 99,  0 ) edit 999999999.99


   let #r-tot       =  #r-tot       +  #d-tot

   let #d-tot       =  0

end-if

let $d-key   =  &e.deptid
do  Get-Dept

end-procedure
..
..

                   Hope that helps in someway.... Tony DeLia

--------------------------------------------------------------------------------------------------


Eric Dimick Eastman wrote:
>
> Hello,
>
> Here is my $.02 worth.  When I have to know in advance when the end of a
> select is coming up, I simply select 1 row ahead of my processing.  So I
> select the first row, do nothing, select the second, process the first
> and so on.  Then after the "end-select" I process the last row.  This
> has only happened a few times.
>
> Why do you want to know when the last row is selected?
>
> Often you can do what you want by using negative positions on a print
> statement (assuming that you are printing a report).
>
> Hope this helps,
> Eric
>
> Boris Nadler wrote:
> >
> > Phil,
> >
> > #END-FILE is used for the flat files processing:
> > "The variable #end-file is set to one (1) if end of file occurs on READ.
> > Otherwise it is set to zero (0). Your program should check this variable
> > after each READ command"
> >
> > I'd use BEGIN-SELECT twice : first to get rows count and then to do special
> > frocessing
> > for the last row. Its not very efficient but I'm not aware about system
> > variable or function
> > you can use for the last row processing in SQR.
> >
> > Hope its help.
> >
> > Boris.   ( Qualcomm Inc., 619/651-8571 )
> >
> > At 04:17 PM 10/7/98 -0400, you wrote:
> > >
> > >
> > >I do believe there is a reserved variable called
> > >
> > >#END-FILE
> > >
> > >that is internally kept within the SQR.  It is set to 1 if the end of file
> > >occurs.
> > >I have never used it, so I can not help you there.
> > >
> > >Hope it helps,
> > >
> > >Cheers,
> > >
> > >Danny
> > >
> > >
> > >
> > >
> > >
> > > (Embedded
> > > image moved   Phil Roell <phil.roell@NATINST.COM>
> > > to file:      10/07/98 03:44 PM
> > > pic25032.pcx)
> > >
> > >
> > >
> > >
> > >Please respond to SQR-USERS@USA.NET
> > >
> > >To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
> > >cc:    (bcc: Daniel Guynes)
> > >Subject:  ? Last Record
> > >
> > >
> > >
> > >
> > >Does SQR provide a flag that tells you when it has selected the last record
> > >in a
> > >select statement?
> > >
> > >Thanks,
> > >
> > >Phil
> > >
> > >
> > >
> > >

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com