RE: SQL within RPG -- RPG400-L

Question is is there any trade off by specifically declaring the ORDER
BY in conjunction with the GROUP BY.  I'd prefer to know beyond the
shadow of a doubt that my data will be in the order I desire.  Should
someone (for whatever reason) delete/modify that logical view what would
happen then?  Would you still receive the same data order as you were
expecting or would it be a "crap shoot"???

Ich sprechen zie kleine Deutsche....but not that much hehe... 


Thanks,
Tommy Holden


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

Hi Tommy,

did you check the used access path, by either using STRDBG or Visual
Explain?

I also started a test:
1. select firnr, lpkndb, lplao, lppln, sum(lpmeg)
   from lllposp
   group by firnr, lpkndb, lplao, lppln

   I got the following joblog entry for my query:
   ****: Debug-Nachrichten des Optimierungsprogramms fur Abfrage  werden
   gestartet.
   Abfrageoptionsdatei kann nicht abgerufen werden.
   Abfrageoptionsdatei kann nicht abgerufen werden.
   Alle Zugriffspfade wurden fur Datei LLLPOSP berucksichtigt.
   Zusatzliche Ursachencodes fur Zugriffspfad verwendet.
   Zugriffspfad der Datei LLLPOS37 wurde von einer Abfrage verwendet.
   Abfrageoptionsdatei kann nicht abgerufen werden.

   Sorry it's German, but you'll see the access path of the
   logical file LLLPOS37 is used. The key fields are:
   FIRNR, LPKNDB, LPLAO, LPPLN, LPSNU, LPBNR

2. select firnr, lpkndb, lplao, lppln, sum(lpmeg)
   from lllposp
   group by firnr, lpkndb, lplao, lppln
   order by firnr, lpkndb, lplao, lppln

   The joblog entry of this second query results confirms,
   that the access path LLLPOS37 is used.

In your example the query optimizer may think, that a table scan is less
expensive than using an access path.
By adding the order by clause, you may affect the optimizer's decision
to
use an existing index.

That's why it's recommended, not to use a order by clause if it's not
neccessary.
And to add an order by clause if a specified sequence is neccessary.

Birgitta


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



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