RE: SQL within RPG

GROUP BY & ORDER BY perform 2 entirely different functions.  Group By
allows you to basically "process by key" criteria for functions (sum(),
etc.) where as ORDER BY allows you to "read by key"

If you use GROUP BY without the ORDER BY when you "read" the data in it
will be processed in RRN order(IIRC).

If you are only selecting a certain cust_id & line_type as described
below you can exclude the ORDER BY since you will only pull the results
for the one GROUP. 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
Sent: Monday, November 14, 2005 9:54 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL within RPG

Thanks Matt, I did not think I could do two order by within the same 
statement.....but that will do what I need.....


On Mon, 14 Nov 2005 10:28:44 -0500, Matt.Haas wrote
> Grouping by the customer id and line type should do this. Something
> along the lines of:
> 
>   SELECT cust_id, line_type, sum(amount)
>     FROM billing_file
> GROUP BY cust_id, line_type
> ORDER BY cust_id, line_type
> 
> For performance reasons, you'll want to create a logical file that 
> has the fields used by your selection criteria followed by the 
> fields used in the ORDER BY clause.
> 
> Matt
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> Sent: Monday, November 14, 2005 9:50 AM
> To: RPG Group
> Subject: SQL within RPG
> 
> I have a program that calculates sales for each of our customers 
> based upon the type of sale it is (defined by a line type), it is 
> using embedded sql and works fine.  What I would like to do is 
> expand it so that I can display for each customer within the subfile 
> their totals sales for two or possibly three seperate line types IE: 
> line haul charges, stop off charges, fuel surcharges.
> 
> Is there a way to do this without having to create a work file or re-
> run the sql query for each line type? What I mean is this, is it 
> possible to select the sums for each of sales for each line type 
> grouped by customer?
> 
> If you bought it, it was hauled by a truck - somewhere, sometime.
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> 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@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-
> L-request@xxxxxxxxxxxx Before posting, please take a moment to 
> review the archives at http://archive.midrange.com/rpg400-l.


If you bought it, it was hauled by a truck - somewhere, sometime.