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

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2020 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].