Re: CPYF = UNION ?

If what you want is the records from one file followed by the records from the other file, you want one of 3 ways -

1. 2 CPYF commands with MBROPT(*REPLACE) on the first CPYF, MBROPT(*ADD) on the 2nd

2. UNION of 2 SELECT statements

3. A single-format LF over 2 members - you can name both of the PFs in the PFILE of a single record format - the manual calls this a simple format LF. Here is a quote from the manual -

"If you specify more than one physical file name for one record format in a multiple format logical file, all fields in the record format for the logical file must exist in all physical files specified."

I believe you can still have key fields in this kind of logical.

I don't really like #3 very much - it kind of hides a lot of what is going on. #1 is also somewhat of a problem to remember.

I like #2 because it has to be clearly stated somewhere - perhaps as a VIEW, where the SELECT will be seen in DSPFD and one hope it came from RUNSQLSTM or RUNSQL in a CL or the like.

So this is a vertical combination, one might say - while a JOIN is a horizontal combination. From your original post, I believe you want the vertical one.

Here is the vertical -

Row 1 from file 1
Row 2 from file 2
Row 1 from file 2
Row 2 from file 1

I mixed up the order, because key fields might do that.

A join takes fields from one file and puts them at the end of fields from another file, based on some matching fields - except for a CROSS JOIN as Alan suggested - I can't believe he was serious - where was the smiley?

So this horizontal arrangement might be like this -

Fields from row 1 of file 1 --> Fields from row 2 of file 2 (file1.field1 = file2.field1 for some value)
Fields from row 5 of file 1 --> Fields from row 3 of file 2 (file1.field1 = file2.field1 for some other value)

A CROSS JOIN __can't__ be what you want, I hope - it means that the fields of every row from the 2nd file are appended to the fields of every row from the 1st file.

So if you have 100 rows in each, you will have 100 X 100 (see the cross in there, on it's side?), or 10,000 in your result set.

This is the kind of advice one gets when the question is vague - I certainly don't enjoy watching those of us who are trying to be helpful, to be stumbling and guessing -

I am sure you, as the OP, have it all in your head - I am that way at times, where I know what I want, I've been thinking about it for a longish time - so I don't explain myself enough -

PLEASE - take time to post more words - Chuck said it well, we can't be as useful to you without better input.
On 10/22/2014 4:43 PM, Hoteltravelfundotcom wrote:

so really i should do a join logical it seems.

On Wed, Oct 22, 2014 at 4:53 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

i see. i'll have to try and see.

On Wed, Oct 22, 2014 at 12:29 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 21-Oct-2014 17:38 -0500, Hoteltravelfundotcom wrote:

I have gotten the data as I need using CPYF.

Now to make this dynamic, I want to use UNION

Will this be the same as CPYF that is, if all fields are the same
lengths and # of fields are the same, we get the add perfectly.

The SQL is much more sophisticated, plus the column names are
immaterial for the UNION query; the column names from the /primary/ file
are used for the result set if they are not specified explicitly. The
allowed /mapping/ and the defined /promotion/ of data types for the SQL
determines if the query is allowed [without explicit casting or other
derivations\expressions] and if so, then also what is the final data type
of each column.

Presuming [based on a prior topic that was posted about] there are two
files with identical column definitions but different column names [e.g.
name prefix betwixt, being the only differences], the UNION query is quite
simple:

select * from first_file
union all
select * from second_file

The CREATE VIEW is a tad more particular with regard to naming the
columns, so a column-list would be required:

create view union_view
( "1stField" for f1
, "2ndField" for f2
, "NthField" for fn
) as
select * from first_file
union all
select * from second_file

--
Regards, Chuck

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