Re: End-of-file on SELECT

[Nathan Stratton Treadway <nathant@ONTKO.COM>]

On Tue, Apr 02, 2002 at 04:55:43PM -0500, Denise White wrote:
> On the top two levels (region_cd & support_team_mbr), I need to page
> break.  On the major level, region_cd, I want to do the page break
> every time - no problem.
>  However, on the support_team_mbr, if it is the last in the region_cd, I only
> want to skip a line and then print the region_cd totals; the page break will
> come after the region_cd totals.  If the support_team_mbr is not the last in 
>the
> region_cd, I do want to do a page break.  Something like this:
>
> [detail lines]
> support_team_mbr 1 totals
>
> region_cd 1 totals
>
> [page break]
>
> [detail lines]
> support_team_mbr 2 totals
>
> [page break]
>
> [detail lines]
> support_team_mbr 3 totals
>
> region_cd 2 totals  (includes support_team_mbr 2 + 3)
>
> So, here are my on-break clauses:
>
> v.region_cd             () on-break print=never after=Region-Brk
>                                 save=$hold_region      level=1
> m.support_team_mbr () on-break print=never after=Team-Mbr-Brk
>                                 save=$hold_team_mbr level=2
>
> In my Team-Mbr-Brk procedure, I have the following:
>
>       if &v.region_cd = $hold_region and not isnull(&v.region_cd)
>             new-page
>          else
>             position (+1)
>          end-if
>
> This works fine, until I hit the end of the select.  There, it prints the last
> support_team_mbr totals, does a page break, and prints the region_cd totals!  
>I
> added the not isnull() condition, thinking that at the end, null values would
> have been returned to signal the program that it was finished, but apparently 
>it
> does not return a null row, but retains the last values.  So, at the end,
> &v.region_cd does equal $hold_region.  Is there anything I can add to the
> condition so that at the end of the select, it falls into the 'else'?  It 
>would
> also help me in my header, so I could blank out some values I am printing in 
>the
> header when I print the grand totals on a final page, and would prefer to not
> see the last values still printing.

A few thoughts:

As an aside: SQR leaves the value of the last row selected in the &
variables so that you can use those values later on in your program.


Normally, the easy way to tell that you have finished processing all the
rows from a select is that program execution drops past the END-SELECT.
This should be good enough to fix the headers on the grand total page:
right after the END-SELECT, but before you actually print the grand
totals, just blank out the variables that you want to surpress from the
header.  That way, when the grand total page is "ejected" and the
BEGIN-HEADING section is printed, those values will be blank.

(Alternatively, you could set a "done-with-select" flag variable, and
then check that variable in the BEGIN-HEADING to decide whether or not
to print certain lines of the heading.)


Controlling when to do the page breaks within the select is more
difficult.  Essentially, you need to know when you print the
support_team_mbr totals if you are going to also be printing the region
totals right afterwards.  A more general way to phrase this question is
"At which break level was this break triggered?" In other words, what
you want to know in the support_team_mbr AFTER procedure (which is
level=2) is whether it is being executed because of a level 2 break
(in which case the level 1 procedure will not be executed, and the page
break should be generated by this procedure) or because of a level 1
break (in which case this procedure should allow the the page break to
be generated by the level 1 procedure).

This is a common situation, but unfortunately SQR does not currently
have a way to give your program this information.  (You'll find this
topic on our Suggested Enhancements page,
http://www.ontko.com/sqr/enhancements.html ; currently it's
Enhancement #5).

I don't know of a good way to tell, from within an AFTER procedure, if
the row of data just processed was the last row or not.  What I do in
this situation is use a flag set in the AFTER procedures to indicate to
the BEFORE procedures that they need to do do the page breaking before
they do the processing they'd normally do.  (Normally I'd call the
"check" procedure from the very top of my existing BEFORE procedures,
but since you don't have any BEFORE procedures you can probably use the
"check" procedure itself as the BEFORE procedures, as I've shown below.)

So, your program would look something like this:

  begin-procedure main

  move 0 to #need_page_break
  begin-select
  v.region_cd             () on-break print=never level=1
                                      before=check_need_page_break
                                      after=after_region
                                      save=$hold_region
  m.support_team_mbr () on-break print=never level=2
                                 before=check_need_page_break
                                 after=after_support_team_mbr
                                 save=$hold_team_mbr

  ETC...
  end-select

  do check_need_page_break

  move '' to $hold_region
  move '' to $hold_team_mbr
  do print_grand_totals
  ETC...
  end-procedure ! main

  begin-procedure after_region
  ! print region totals
  ETC....
  move 1 to #need_page_break
  end-procedure ! after_region

  begin-procedure after_support_team_mbr
  ! print support team mbr totals
  ETC....
  move 1 to #need_page_break
  end-procedure ! after_support_team_mbr

  begin-procedure check_need_page_break
  if #need_page_break
    new-page
    move 0 to #need_page_break
  end-if
  end-procedure ! after_support_team_mbr


Note that you need a final "check" call after the END-SELECT to be sure
that the final page break is generated before the grant total page is
printed.  Also, you need to make sure the "check" call is invoked before
anything is printed in any BEFORE procedure at any level, or things will
start showing up on the wrong page.

This approach works because one of the BEFORE procedures is always the
first thing to execute after the last AFTER procedure executes in a
particular cycle of the BREAK processing (*). We can't tell from within
the AFTER if another AFTER procedure is just about to be executed, but
once we start processing the BEFOREs, we know all the AFTERs have
completed.  If the first thing the BEFORE procedure does is check to see
if the page break is needed, then the page break will always happen
immediately following of the last AFTER procedure that got executed on
this cycle.

(*) except when the AFTER is from the last row of data -- that's why we
need another "check" after the END_SELECT


Hope this makes sense....  Let me know if I can explain any aspect
further.

                                                        Nathan

----------------------------------------------------------------------------
Nathan Stratton Treadway  | Ray Ontko & Co. | Software consulting services
nathant@ontko.com         | Richmond, IN    | http://www.ontko.com/