Re: Break Logic ????

[Allen Wong <TLR585J@BAPLAZA.BELL-ATL.COM>]

     Let me rephrase my problem, the "before" data is composed of
     main-select for 'Product,Product Name,Product Plan' and the
     amount field is retrieved by another function.

     sample code:

     begin-select
     product
     productname
     productplan
     productid

        DO get_amount(&product_id,#amount)
     !I want to print the breaks after retrieving the amounts
        PRINT   &product           on-break level=1
        PRINT   &productname       on-break level=2
        PRINT   &productplan       on-break level=3
        PRINT   #amount
     !I want the sum of the amount based on the break
     from products
     end-select

     BEGIN-PROCEDURE get_amount(#product_id,:#amount)
     BEGIN-SQL
        BEGIN
                SELECT sum(amount)
                 INTO  #amount
                 FROM  other_table
                 WHERE prod_id = #product_id;;
        END;;
     END-SQL
     END-PROCEDURE


     Note in my real code I have multiple sub-procedures that perform
     calculations for different amounts. eg. adjustment, advance, total
     amt, etc. -

     Thanks.


______________________________ Reply Separator _________________________________
Subject: Break Logic ????
Author:  owner-sqr-users@USA.NET at INTERNET
Date:    5/7/97 1:37 AM


Attachment file number 1, file name
Creation date:  May 07, 1997 02:06:15
Document Type:  ASCI, Document Class: MEMO
Author:
Comment:        Break Logic ????
Level:          1, Number : 1



     I have the following data which is created by a main select which
     branches off to many subselects

     before:

     Product            Product Name    Product Plan          Amount
     Car                Camry           2-door                 100
     Car                Camry           2-door                 300
     Car                Camry           4-door                 400
     Car                Camry           4-door                 500
     Car                Camry           4-door                 600
     Truck              Jeep            2-door                 200
     Truck              Jeep            2-door                 300
     Truck              Jeep            4-door                 400
     Truck              Jeep LE         4-door                 600


     I would like the formatted output to be:

     Product            Product Name    Product Plan          Amount
     Car                Camry           2-door                 400
                                        4-door                 1500
     Truck              Jeep            2-door                 500
                                        4-door                 400
                        Jeep LE         4-door                 600

     I tried using the on-break,{I have not tried the after/before to
     calculate the totals}, but it prints a line for the repeating items, I
     know I can use a hack by inserting these values into a temporary table
     and use sql "group by's" to get the results and then formatted in sqr.

     any suggestions.

     thanks.

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