RE: Using SQl to obtain a field value from one file or another

Thanks Rob
Let me give that a try

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, June 30, 2017 2:00 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Using SQl to obtain a field value from one file or another

with T1 as (
select f1.field1, f2.field2 as col2
from f1 left inner join f2 on f1.field1 = f2.field1 union all select f1.field1, f3.field2 as col2 from f1 left inner join f3 on f1.field1 = f3.field2) select t1.field1, t1.col2 from t1 order by t1.field1

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 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: Alan Shore <ashore@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 06/30/2017 01:50 PM
Subject: Using SQl to obtain a field value from one file or another
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Hi everyone
Before I forget - we are on V7r1
Happy 4th July weekend to the yanks
I may be pushing my luck - Friday afternoon before what could be a 4 day
weekend for many people
But - here goes
I will try and keep this as simple as possible

I have 3 files
File-1 has one field with values
A
B
C
D
E
Etc.

File-2 contains 2 fields
Field 1 contains an alpha character with SOME of the values that are in
file-1
Field 2 is just data

File-3 also contains 2 fields
Field 1 contains an alpha character with SOME of the values that are in
file-1
Field 2 is just data

NOTE - the values A,B,C,D, E etc. will be in either File-2 or File-3 but
NOT both

How can I combine whats in file-1 with File-2 field 2 and File-3 field 2
but in ONLY 2 columns
For example - this is what I am looking to produce from an SQL statement

A Field2 from File-2
B Field2 from File-2
C Field2 from File-3
D Field2 from File-3
E Field2 from File-2

I tried the following
Select File-1.Field1, File-2.Field2, File-3.Field2 from File-1 left join
File-2 on
File-1.Field1 = File-2.Field1
left join File-3 on
File-1.Field1 = File-3.Field1

But that results in three columns - not 2

As always - all responses gratefully accepted

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill