Umm Okay, Thank you, I Guess


On Fri, Mar 9, 2012 at 10:30 AM, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Michael,

IIRC, SQL specs state that the only way to be sure that a result is ordered
is ny using the ORDER BY clause.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Fri, Mar 9, 2012 at 10:57 AM, Michael Schutte <mschutte369@xxxxxxxxx
wrote:

No problem, not sure why it's ignored, I've had times when I wanted it
ordered.


On Fri, Mar 9, 2012 at 9:13 AM, Gary Thompson <gthompson@xxxxxxxxxxx>
wrote:

Michael, thank you, I did not know that, and I appreciate the demo.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Friday, March 09, 2012 6:32 AM
To: Midrange Systems Technical Discussion
Subject: Re: sql v opnqryf

Gary, I believe that the order by inside a CTE is ignored.

i.e. with this query, it's still in RRN order.

WITH cte as (
SELECT rrn(build) rrn, build.*
FROM build
ORDER BY BUNAME)
SELECT *
FROM cte

RRN Act/Del Building Building
Code Number Name

1 2 WAUKESHA WI BLDG 2
2 3 WAUKESHA WI BLDG 3
3 6 ANNEX
4 16 ANNEX
5 25 CHINO CALIF
6 35 SAN BERNARDINO
7 50 1650
8 53 ORD GATEWAY - CHICAGO
9 54 1654
10 60 1600
11 69 RICKENBACKER
12 75 1675 - WATKINS
13 80 1580
14 90 1590


On Thu, Mar 8, 2012 at 11:01 AM, <rob@xxxxxxxxx> wrote:

Yeah, once you start replacing OPNQRYF with SQL you don't go back.


Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Dave <dfx1@xxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <
midrange-l@xxxxxxxxxxxx
,
Date: 03/08/2012 10:01 AM
Subject: Re: sql v opnqryf
Sent by: midrange-l-bounces@xxxxxxxxxxxx



I was just idly wondering if I could replace the opnqryf and/or the
rpg.
Anyway, I did the opnqryf, put all the keys in ascending order and
got
an input output error from the rpg that I didn't understand.
Apparently the key of the file was different from the key of the file
that was already opened.
I ended up copying the dds of the file into my test library, changing
the field to ascend, which does not alter the level, and using chgpf
before running the matching record program. What a bodger!

Le 8 mars 2012 15:41, sjl <sjl_abc@xxxxxxxxxxx> a écrit :

IMO before offering any advice I would need for Dave to clarify
some
ambiguity in his question.

Dave:
Do you want to /replace/ the matching-record logic program with a
SQL
file
comparison, or do you want to only replace the OPNQRYF step in the
job
with
SQL ?

If you are trying to go pure SQL to join and compare the files,
having duplicate keys in the files will be a problem.

- sjl


Gary wrote:
Use a Common Table Expression to order file 1 in the preferred
sequence, then join to the CTE, returning the matched rows as one
long record that is mapped into two data structures then make
comparisons between the two data structures ?


Dave wrote:
Hi all,

I want to compare two files : the results of a test against the
results
of
the last test. We have an old home utility that generates an rpg
matching
record program. When there are key fields in both ascending and
descending
order, we use opnqryf in a clp that calls the rpg to temporarily
reorganize
the files so that matching will work.

How could this be done in sql?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a
moment to review the archives at
http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].