philpl1jb Apr 5, 2013   0:52 AM GMT

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.

54,090 pointsBadges:

philpl1jb Apr 5, 2013   0:59 AM GMT

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

54,090 pointsBadges:

TomLiotta Apr 5, 2013   7:10 AM GMT

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

125,585 pointsBadges:

depeknow Apr 6, 2013   6:53 AM GMT

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

40 pointsBadges:

philpl1jb Apr 7, 2013   10:44 AM GMT

Looks like you're done it. What issues are outstanding?

54,090 pointsBadges:

TomLiotta Apr 7, 2013   10:53 AM GMT

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

125,585 pointsBadges:

philpl1jb Apr 7, 2013   11:07 AM GMT

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.

54,090 pointsBadges:

philpl1jb Apr 7, 2013   4:33 PM GMT

oh, I thought that was from the op

54,090 pointsBadges: