Re: Merge of 2 similar files

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