Discussion:
CPYF = UNION ?
(too old to reply)
Hoteltravelfundotcom
2014-10-21 22:38:55 UTC
Permalink
Raw Message
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Alan Campin
2014-10-21 22:51:29 UTC
Permalink
Raw Message
So you are doing this in SQL? Are you doing this manually in STRSQL or want
to do in batch?

In batch you counld create a QM Query with your union and RUNQMQRY with an
*OUTFILE and should give you the same thing.

On Tue, Oct 21, 2014 at 4:38 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-21 22:55:36 UTC
Permalink
Raw Message
I really need more information - I'll take a stab at something, however.

CPYF is a command, UNION is an SQL keyword.

There is nothing about UNION that makes things any more dynamic, so far
as I can see.

But I don't know where you are getting the data.

Besides, look at the MBROPT parameter of CPYF.
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Hoteltravelfundotcom
2014-10-21 22:59:39 UTC
Permalink
Raw Message
i want to create a view that would do a union on 2 logical views.
basically, in our system, we have open orders in 2 places. in the details
file and in history. I need to report on all open orders. it was fine doing
the 'old' way
of 2 queries and then the CPYF.

I changed the queries to views. now i want to add them together.
Post by Vernon Hamberg
I really need more information - I'll take a stab at something, however.
CPYF is a command, UNION is an SQL keyword.
There is nothing about UNION that makes things any more dynamic, so far as
I can see.
But I don't know where you are getting the data.
Besides, look at the MBROPT parameter of CPYF.
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Hoteltravelfundotcom
2014-10-21 23:00:09 UTC
Permalink
Raw Message
sorry correction, i mean closed orders not open.

On Tue, Oct 21, 2014 at 6:59 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
i want to create a view that would do a union on 2 logical views.
basically, in our system, we have open orders in 2 places. in the details
file and in history. I need to report on all open orders. it was fine doing
the 'old' way
of 2 queries and then the CPYF.
I changed the queries to views. now i want to add them together.
Post by Vernon Hamberg
I really need more information - I'll take a stab at something, however.
CPYF is a command, UNION is an SQL keyword.
There is nothing about UNION that makes things any more dynamic, so far
as I can see.
But I don't know where you are getting the data.
Besides, look at the MBROPT parameter of CPYF.
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-22 00:58:57 UTC
Permalink
Raw Message
Thx, that's going to work just fine.

Just as a thought - do you need the views for separate usage? If not,
you could put the SELECT statements that define the views into one view
with a UNION of them - of course, you may already have done that!!

Regards
Vern
Post by Hoteltravelfundotcom
i want to create a view that would do a union on 2 logical views.
basically, in our system, we have open orders in 2 places. in the details
file and in history. I need to report on all open orders. it was fine doing
the 'old' way
of 2 queries and then the CPYF.
I changed the queries to views. now i want to add them together.
Post by Vernon Hamberg
I really need more information - I'll take a stab at something, however.
CPYF is a command, UNION is an SQL keyword.
There is nothing about UNION that makes things any more dynamic, so far as
I can see.
But I don't know where you are getting the data.
Besides, look at the MBROPT parameter of CPYF.
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
PaulMmn
2014-10-22 03:56:35 UTC
Permalink
Raw Message
Of course, you could create a single Join Logical over both physicals...

--Paul E Musselman
Paulmmn-vB5rODKJlotqwBYvR5I4hVaTQe2KTcn/@public.gmane.org

.
Post by Vernon Hamberg
Thx, that's going to work just fine.
Just as a thought - do you need the views for separate usage? If
not, you could put the SELECT statements that define the views into
one view with a UNION of them - of course, you may already have done
that!!
Regards
Vern
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-22 13:48:52 UTC
Permalink
Raw Message
Technically, it's not a JOIN logical...he's not joining.

It'd be a "simple" single format logical with two files specified via
PFILE() on a single record format.

In contrast, a "multi-format" logical that has multiple formats each with
it's own PFILE().
Post by PaulMmn
Of course, you could create a single Join Logical over both physicals...
--Paul E Musselman
.
Post by Vernon Hamberg
Thx, that's going to work just fine.
Just as a thought - do you need the views for separate usage? If not, you
could put the SELECT statements that define the views into one view with a
UNION of them - of course, you may already have done that!!
Regards
Vern
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Glenn Gundermann
2014-10-22 01:37:17 UTC
Permalink
Raw Message
Something like this maybe:

With order_data as (
Select dord#, dordl#, dqty, dprice
From order_details
Union all
Select hord# as dord#, hordl# as dordl#, hqty as dqty, hprice as dprice
From order_history)

Select column-list
From order_data a
Join other-files
Where condition
Order by order-list;

-----Original Message-----
From: Hoteltravelfundotcom <hoteltravelfun-***@public.gmane.org>
Sender: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>Date: Tue, 21 Oct 2014 18:59:39
To: Midrange Systems Technical Discussion<midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Reply-To: Midrange Systems Technical Discussion <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Subject: Re: CPYF = UNION ?

i want to create a view that would do a union on 2 logical views.
basically, in our system, we have open orders in 2 places. in the details
file and in history. I need to report on all open orders. it was fine doing
the 'old' way
of 2 queries and then the CPYF.

I changed the queries to views. now i want to add them together.
Post by Vernon Hamberg
I really need more information - I'll take a stab at something, however.
CPYF is a command, UNION is an SQL keyword.
There is nothing about UNION that makes things any more dynamic, so far as
I can see.
But I don't know where you are getting the data.
Besides, look at the MBROPT parameter of CPYF.
Post by Hoteltravelfundotcom
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 legnths
and
# of fields are the same, we get the add perfectly.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
CRPence
2014-10-22 16:29:51 UTC
Permalink
Raw Message
Post by Hoteltravelfundotcom
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Hoteltravelfundotcom
2014-10-22 20:53:45 UTC
Permalink
Raw Message
i see. i'll have to try and see.
Post by Hoteltravelfundotcom
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
select * from first_file
union all
select * from second_file
The CREATE VIEW is a tad more particular with regard to naming the
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Hoteltravelfundotcom
2014-10-22 21:43:43 UTC
Permalink
Raw Message
so really i should do a join logical it seems.

On Wed, Oct 22, 2014 at 4:53 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
i see. i'll have to try and see.
Post by CRPence
Post by Hoteltravelfundotcom
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
select * from first_file
union all
select * from second_file
The CREATE VIEW is a tad more particular with regard to naming the
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Alan Campin
2014-10-22 21:58:52 UTC
Permalink
Raw Message
If you do I think you want a cross join.

http://www-01.ibm.com/support/knowledgecenter/ssw_i5_54/sqlp/rbafycrojo.htm


On Wed, Oct 22, 2014 at 3:43 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
so really i should do a join logical it seems.
On Wed, Oct 22, 2014 at 4:53 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
i see. i'll have to try and see.
Post by CRPence
Post by Hoteltravelfundotcom
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
Post by Hoteltravelfundotcom
Post by CRPence
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
Post by Hoteltravelfundotcom
Post by CRPence
select * from first_file
union all
select * from second_file
The CREATE VIEW is a tad more particular with regard to naming the
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-22 23:18:21 UTC
Permalink
Raw Message
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.
Post by Hoteltravelfundotcom
so really i should do a join logical it seems.
On Wed, Oct 22, 2014 at 4:53 PM, Hoteltravelfundotcom <
Post by Hoteltravelfundotcom
i see. i'll have to try and see.
Post by CRPence
Post by Hoteltravelfundotcom
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
select * from first_file
union all
select * from second_file
The CREATE VIEW is a tad more particular with regard to naming the
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
CRPence
2014-10-22 22:35:33 UTC
Permalink
Raw Message
So really I should do a join logical it seems.
Not sure what precipitated that followup reply. But if that is the
[new] scenario, then perhaps there would be some value in starting a new
thread with sufficient details about the specific scenario so that a
reader can _see_ and understand the DDL, the relations, some example
data, and example expected output.

This thread and some of the replies seem somewhat typical of an
ill-defined topic\scenario; i.e. too much inference\supposition is
required by the readers, and thus their different assumptions resulted
in [sometimes wildly] diverse responses. Not a big surprise given the
dearth of specific details offered in the OP for this thread.

Given the Copy File (CPYF) has no capability that can be mistaken for
the capability of a JOIN, though can effect something quite similar to a
UNION, the current "Subject:" does not even give a hint as to what might
be the new topic. Of course IMO, the lack of any explanation, the
mention of CPYF in the OP was less that helpful; almost a distraction.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Loading...