RE: Join 2 Logical Files

[Alan Campin <Alan.Campin@xxxxxxxxxxxxx>]

join will not work in OPNQRYF or SQL. Problem is:

File 1 has key of Key1, Key5, Key8(Say customer number).
File 2 has key of Key1, Key3, Key9

Join would only read in records where there was a match between the key in
file one and the key in file two. Records Key3, Key9 would not be included
because there was no match. Got burnt on this one myself a few times.

Just checked the manual(Should have done that first. Thought the AS/400
didn't support it). What could be done is a CROSS JOIN. Will take every
record from File one and concatenate with every record from file two. 

I assume (have not done) that you would get a record like this:

Key1 Value from File one.
Key1 Value from File two.

Key3 Value from File one. Blank. No join.
Key3 Value from File two.  

Key5 value from File one. 
Key5 value from file two. Blank. No join.

etc. 

One concern here is if you do not have unique key on the files. 

For example, if you use customer number and you have 5 records with the same
customer number in file one and 3 records in file two. 

The join would return 15 records I think. Read first record from file one
and then join to 3 records in file two and then second record join to 3 from
the second, etc. 

Ouch. could get very interesting. 
 
-----Original Message-----
From: alan shore [mailto:SHOREA@dime.com]
Sent: Thursday, January 13, 2000 1:17 PM
To: RPG400-L@midrange.com
Subject: Re: Join 2 Logical Files


Try using a join (inner, outer or partial) using the CL command OPNQRYF. I'm
assuming there is some key or keys common to both files. The result of this
is one file that can be read from beginning to end, or use the natural
cycle.

>>> <Gade_R_Reddy@consecofinance.com> 01/13 2:41 PM >>>

Hey Guys,

I have two logical files and my report is based on the union of all the
data  from these two logical files.

Now in my program i read the two logical files from BOF to EOF & created a
physical file with the all data from the logical files & drive the report
from there.

Please suggest are there anyways to get the union of two logical files and
how are SQL Views more advantages than Logical Files????

Thanks.
Gade.


+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com 
+---

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---