RE: SQL within RPG

Pretty sure I just ran a test:

First SQL with no ORDER BY clause:
select s2code,s2keym       
 from clocn0009/cksecrt2   
group by s2code, s2keym    

Results appear like so:
FIELD  FIELD 
CODE   VALUE 
  J    83052 
  J    83552 
  S    00009 
  J    90852 
  J    91052 
  S    08897 
  J    72152 
  J    73152 
  J    75952 
  J    76152 
  J    76252 
  J    76652 

Now I Add the order by clause:
select s2code,s2keym     
 from clocn0009/cksecrt2 
group by s2code, s2keym  
order by s2code,s2keym   

Results are like so:
FIELD  FIELD 
CODE   VALUE 
  J    60052 
  J    60552 
  J    60652 
  J    60952 
  J    61052 
  J    61652 
  J    63252 
  J    64052 
  J    65152 
  J    65252 
  J    65352 
  J    66052 
  J    67052 
  J    67152 
  J    68852  


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of HauserSSS
Sent: Monday, November 14, 2005 1:15 PM
To: RPG programming on the AS400 / iSeries
Subject: AW: SQL within RPG

Hi Tommy,

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

Sure?

The following text is an extract from
"Indexing and Statistics Strategies for DB2 UDB for iSeries" by Michael
Caine
http://www-03.ibm.com/servers/enable/site/education/abstracts/indxng_abs
.htm
l:

Grouping and Ordering
Other common functions within an SQL request are grouping and ordering.
Using
the SQL GROUP BY clause, queries will summarize or aggregate a set of
rows
together. In DB2 UDB for iSeries, the optimizer can use either an index
or a
hashing algorithm to perform grouping. The method that the optimizer
picks
is
query and system dependent; the optimizer will make its selection based
on
the
nature of the query, the data, and the system resources available.

When a query includes an ORDER BY clause, the database engine will order
the
result set based on the columns in the ORDER BY clause. In DB2 UDB for
iSeries, the optimizer can use either an index or a sort. Therefore,
indexes
can be
used for this function as well. Sometimes, the ORDER BY clause includes
columns already used in the selection and grouping clauses, so the
optimizer
may take advantage of the "by key" processing used for other parts of
the
query
request. The data is processed in order, so to speak.

Birgitta

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Holden Tommy
Gesendet: Montag, 14. November 2005 17:20
An: RPG programming on the AS400 / iSeries
Betreff: 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.

--
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.