I want to merg data from 2 different files. 1 column in both files has same type of data but the headers (Library and Libraries) are different and the fieldnames (ODLBNM & MLLIB). Is there a way to merge both columns into 1 column?
I can't think of a way to do this in query/400.
Here are a few ways to do that:
Using SqL you can create a union --
- QMQRY or as an SQL Statement or an interactive SQL
or
You can create a multi-format logical file.
Yes .. in 3 queries
1 extract just the one field from the first file into output file f1
2 extract just the one field from the second file into output file f2
3 copy f1 to f2 adding the records (CPYF)
4 query f2 it should now contain the fields from both files
It's not clear what you mean by "merge columns". What are you thinking that "merge" means? The column names generally aren't as important as the column values are. Both contain library names. If the values are the same, it doesn't matter which column value (or name) is used. And if the values are different, then the values don't go together. Please give example rows from the two files and show how you think the result set should look. That will us know what you really want to do. -- Tom
Here's a non-sense example:
lib1/head Invoice heads with some fields and CustomerId
lib2/rows Invoice rows with some fields and ItemId
I need a new file with CustomerId_ItemId (?)
insert into mylib/mynewfile (
select concat(trim(CustomerId), concat('_', trim(ItemId))) as cust_item
from lib2/rows r
join lib1/heads h on r.IdDoc=h.IdDoc )
---
Depe
Looks like who's done it? If the example above is what the question was asking, it doesn't make sense. What good is concatenating the library name to itself? My understanding is that the result set should have both copies of the name in the same column but different rows. That's not difficult, but still doesn't seem to have a point. -- Tom
If you can do this in sql why would you want to do this in query/400?
In query/400:
.. select the two files and identify the join fields and that you want a matching join
.. create a result field
|| is used for concat
CustomerId || ‘_’ || ItemId
Select the concat field
Output to a new file.
I don't see a trim type function in the documentation.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 8  Replies