RE: RE: embedded SQL Precompiler

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

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
Phil, attached is an SQL script (in Text format) that i use at out shop for
training purposes. Essentially, in order to overcome the problem of not
having GROUP BY ROLLUP, CUBE, etc., we used unions to generate control
breaks along with the details.
 If you want a complete SQLRPGLE program, let me know and I will email you
our report template.



-----Original Message-----
From: Phil Groschwitz [mailto:sublime78ska@yahoo.com]
Sent: Thursday, February 21, 2002 10:25 AM
To: rpg400-l@midrange.com
Subject: RE: RE: embedded SQL Precompiler


Ramanujam,

Could you post more of an example?  I'd like to learn
how to do this.  Up to now I've created a temporary
table.

Phil

--- "Mangavalli, Ramanujam" <RamM@Mvmills.com> wrote:
> I use (Mostly for reports)
> WITH Temp_Result_Set as
> (select column1 as temp_col1, column2 as temp_col2
>  from tablea
>  where  column1 in (value1, value2)....
> )
> select temp_col1, temp_col2
> from temp_result_set
> group by temp_col1
> ....
> ....
>
> I did have some applications where this did not help
> and had to create a
> temp table or a view.
>
>
>
> -----Original Message-----
> From: Mike Naughton [mailto:mnaughton@juddwire.com]
> Sent: Thursday, February 21, 2002 9:49 AM
> To: rpg400-l@midrange.com
> Subject: Re: RE: embedded SQL Precompiler
>
>
> Joe -
> I'll be really surprised if you haven't already
> thought of this, so maybe
> I'm missing something basic, but I think this can
> actually be done fairly
> easily.
>
> First, it sounds as if the basic requirement is that
> the results of the
> first "select" must stick around somewhere so that
> they can be available
> for future selects. Why not just create a file in
> QTEMP, either by using
> CRTPF (or CRTDUPOBJ), or directly with an SQL
> statement (I guess this
> would be instead of creating a view, and I admit I'm
> not too clear on why
> you might or might not want a view)? Once you have
> the file, your first
> SQL select can insert into it (can't it?), and if
> the file is keyed on
> customer number then listing all the customers and
> then going back and
> retrieving the info is simple using either SQL or
> READs and CHAINs. (If
> the file can't be keyed for some reason (?), then
> SQL would still work).
>
> I guess a downside, performance-wise, might be the
> overhead of creating
> the file in the first place -- some of the more
> adept programmers on the
> list might suggest something fancier using a user
> space, maybe?
>
> Seems simple enough to me (so I've _gotta_ be
> missing something. . . . )
> rpg400-l@midrange.com writes:
> >On the other hand, here's a feature that would be
> beyond either natigve
> >I/O
> >or the current state of SQL affairs: create a
> subset of records from an
> >existing result set.  Now THAT would be nice.
> Here's the business issue:
> >
> >1. Create a view of, say, invoices that were over
> 90 days, by customer and
> >date
> >2. Subselect out just the customer numbers from
> that set, showing them to
> >the user
> >3. Let the user pick a customer, and go back to the
> original result set
> >and
> >then subselect for just that customer
>
>
> Mike Naughton
> Senior Programmer/Analyst
> Judd Wire, Inc.
> 124 Turnpike Road
> Turners Falls, MA  01376
> 413-863-4357 x444
> mnaughton@juddwire.com
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries
> (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/rpg400-l.
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries
> (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/rpg400-l.
>


__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
/* 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
;