He-heh... I thought about replying similarly, after seeing Paul's
reply :-)
But that UNION query example, is little different than anything
already offered. Also note that the explicit DISTINCT processing in
each SELECT is moot, redundant actually, because the UNION as coded
effects UNION DISTINCT over all of the data. Also, that UNION query
still would not reflect what the more recent message had implied; or at
least not reflect what Paul seemed to have inferred was the
expressed-as-desired effect, that all of the original columns names
should appear as duplicates along with all of the renamed columns.
But... The *appearance* of those renamed /fields/ could be effected
in conjunction with the original fields, only in reporting [i.e. as the
/labels/ vs actual /field names/ that would be reflected in the report]
as apparent "duplicate" names, at least in a typical report writer,
using a variant of the following SELECT query:
select item1, iteem1 as item1
, item2, iteem2 as item2
, item3, iteem3 as item3
, a.other, b.other as other
from file1 a, file2 b /* warning: cartesian product */
The interactive SQL reporting would show something like the
following, which matches the list of all of the column names duplicated
in the described order:
....+....1....+....2....+....3....+....4....+....5....
ITEM1 ITEM1 ITEM2 ITEM2 ITEM3 ITEM3 OTHER OTHER
******** End of data ********
But of course a CREATE TABLE "NEW FILE" AS the above SELECT query
would fail with SQL0153 "Column list required", for which the next issue
necessarily would be exactly as Paul alluded; i.e. all of the "Field
names need to be unique." Thus I figured offering the above query as an
apparent resolution would be disingenuous :-)
Both Buck and I have tried to get the OP to more thoroughly define
their scenario, both the givens\knowns and the requirements, so as to
avoid all of the worthless banter that so often results with such
ill-defined problems\endeavors. But so far, no luck in that regard,
so... ;-)
Regards, Chuck
On 27 Jun 2013 09:08, Bill Meecham wrote:
To just report:
SELECT DISTINCT ITEM1 AS I1, ITEM2 AS I2, ITEM3 AS I3, OTHER1 AS O1
FROM FILE1 A
UNION
SELECT DISTINCT ITEEM1 AS I1, ITEEM2 AS I2, ITEEM3 AS I3, OTHER1 AS O1
FROM FILE2 B
ORDER BY I1, I2, I3
paultherrien on Wednesday, June 26, 2013 7:32 PM wrote:
Field names need to be unique.
Hoteltravelfundotcom on Wednesday, June 26, 2013 8:14 AM
I want to merge the data from 2 PF but having the same field
names in the new file.
file1
ITEM1
ITEM2
ITEM3
OTHER1
FIle2
ITEEM1
ITEEM2
ITEEM3
OTHER1
NEW FILE:
ITEM1
ITEM1 (*** was called ITEEM1)
ITEM2
ITEM2 (** Was called ITEEM2)
ITEM3
ITEM3 (** WAS called ITEEM3)
OTHER1
OTHER1
midrange.com
