RE: Difference bet. Primary and Full procedural file.

["Mangavalli, Ramanujam" <RamM@xxxxxxxxxxx>]

Here is how I do control breaks in SQLRPGLE programs:

I can define my WITH AS statement as the base Result Set and introduce my
control breaks on top of this base result set.

I think you guys are making too much out of the Cycle vs Non-cycle debate.
Every way of doing things has its pros and cons.



/* Create a table that has product, the Unit of UOM, produced by plant,
quantity produced and the date produced */

CREATE TABLE    TABLEA (        PRODUCTID CHARACTER (10 ) NOT NULL,
                        PRODUCTUOM CHARACTER (2 ) NOT NULL,
                        PRODPLANT CHARACTER (7 ) NOT  NULL,
                        QUANTITY NUMERIC (11 , 4) NOT NULL,
                        PRODDATE DATE NOT NULL);

/* Insert values into this table */
 INSERT INTO TABLEA VALUES('100001', 'EA', '0000010',  1000.0000,
current_Date);

 INSERT INTO TABLEA VALUES('100001', 'TN', '0000010',   500.0000,
current_Date)  ;

 INSERT INTO TABLEA VALUES('100001', 'TN', '0000010',   500.0000,
current_Date)   ;

 INSERT INTO TABLEA VALUES('100001', 'BA', '0000010',   200.0000,
current_Date)    ;

INSERT INTO TABLEA VALUES('100002', 'BX', '0000010',1500.0000, current_Date)
;

INSERT INTO TABLEA VALUES('100002', 'TN', '0000010',1600.0000, current_Date)
;

INSERT INTO TABLEA VALUES('100003', 'BX', '0000020',  20.0000, current_Date)
;

INSERT INTO TABLEA VALUES('100006', 'AB', '0000020',1500.0000, current_Date)
;

INSERT INTO TABLEA VALUES('100006', 'CN', '0000020',1250.0000, current_Date)
;

INSERT INTO TABLEA VALUES('100006', 'TB', '0000020', 1450.0000,
current_Date)           ;

 INSERT INTO TABLEA VALUES('100008', 'TB', '0000020',  1721.0000,
current_Date)          ;

 INSERT INTO TABLEA VALUES('100001', 'TB', '0000030',  1350.0000,
current_Date)           ;

 INSERT INTO TABLEA VALUES('100001', 'CN', '0000030',  1350.0000,
current_Date)           ;

 INSERT INTO TABLEA VALUES('100002', 'CN', '0000030',  1455.0000,
current_Date)            ;

INSERT INTO TABLEA VALUES('100002', 'CN', '0000030',  1620.0000,
current_Date) ;

INSERT INTO TABLEA VALUES('100003', 'BX', '0000040',  1500.0000,
current_Date)   ;

/* User wants to see a report that contains all the information from this
table. User wants */
/* control breaks by Producing plant and product. Within each of these
control breaks, */
/* user also wants to see a sub control break by UOM meaning that he wants
to see */
/* totals by UOM under each plant and product along with the group totals */


with temp_result_Set as (       select  productid as Product,
                        productuom as ProdUOM,
                        prodplant as Producing_Plant,
                        sum(quantity) as Total_Quantity,
                        CHAR(proddate,USA) as Date_Produced
                from    tablea

                group by        prodplant,
                        productid,
                        productuom,
                        proddate
                          )

( select        'Totals for Plant: ' || trim(producing_plant)  as
Level_Break,
        Producing_Plant,
        X'FF' as Product,
        X'FF' As ProdUom,
                sum(Total_Quantity) as Total_Quantity,
        X'FF' as Date_Produced
  from  temp_result_set
  group by      producing_Plant)

UNION

( select        'Totals for Plant ' || trim(producing_plant) || ' UOM: ' ||
trim(produom) as level_break,
        Producing_Plant,
        X'FF' as Product,
                ProdUom,
        sum(Total_Quantity) as Total_Quantity,
                X'FF' as Date_Produced
  from  temp_result_set
  group by      producing_Plant,
        ProdUOM
)

UNION

( select        'Totals for Product: '||trim(Product)  as level_break,
                Producing_Plant,
                Product,
                X'FF' As ProdUom,
                sum(Total_Quantity) as Total_Quantity,
                X'FF' as Date_Produced
  from  temp_result_set
  group by      producing_Plant,
        Product
)

union

( select        'Totals for Product:' || trim(product) || ' UOM:' ||
trim(produom) as level_break,
                Producing_Plant,
        Product,
        ProdUom,
        sum(Total_Quantity) as Total_Quantity,
                X'FF' as Date_Produced
  from  temp_result_set
  group by      producing_Plant,
        product,
        produom
 )
 UNION
( select        ' '      as Level_Break,
        Producing_Plant,
                Product,
                ProdUom,
                sum(Total_Quantity) as Total_Quantity,
                Date_Produced
   from         temp_result_set
   group by     producing_Plant,
        product,
        produom,
        date_produced
 )
 order by       producing_plant,
        product,
        produom,
        date_produced,
        level_break
;