× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: RE: Join 2 Logical Files
  • From: "alan shore" <SHOREA@xxxxxxxx>
  • Date: Thu, 13 Jan 2000 16:18:56 -0500

In the example you provided, you could also use QUERY/400. You could only join 
the two logicals on key1, the resultant output file would give the results you 
stipulated. If Key 1 is the only commonality, then that can be the only key you 
can use. It would depend on what results the user requesting this, was seeking.

>>> Alan Campin <Alan.Campin@CaseLogic.com> 01/13 3:47 PM >>>
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 
+---

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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

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

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.