Re: Multi Level ON-BREAK

[Ray Ontko <rayo@ONTKO.COM>]

> I am a new SQR programmer using SQR3.  Currently, I am creating a
> program that produces a count of UNIQUE PRODUCTS for each ACCOUNT.  A
> product is identified by 3 fields on a table.
>
> I have created a control-break process to accomplish this task.  My
> break is on the ACCOUNT and the CONCATENATED PRODUCT (the 3 product
> fields).
>
> Can someone tell me if the above can be done with the ON-BREAK
> command and if so an example would be appreciated.  Also, which would
> be more efficient when the query has the potential of capture more
> than 50,000 records. Creating a control-break process or using the ON-
> BREAK command.
>
> Thanks,
> Raline Reid

Raline,

I'm assuming you're simply trying to produce a count of distinct
products by account (but not actually list the products).  If
so, then if you're using a SQL database that supports GROUP BY,
something like this should work (on-break not needed):

   begin-select
   account
   count(distinct a||b||c)
   from ...
   group by account
   end-select

If you're trying to list them:

   begin-select distinct
   account () on-break after=a before=b
   a||b||c
      add 1 to #count
   from ...
   end-select

This assumes that you'll use procedures a and b to print what you
want each time the account number changes, and reset the #count to
0.

If you want to do your own control break logic, I would not
expect that it would be much different from a performance
standpoint.

If you want to print a detail report with a final summary page,
you'll have to use arrays to save the result for each account
into the array for printing the summary, or something like that.

Does this help?

Ray
----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Ask me about SQR and the WEB"
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/