CPYF Command in AS/400

895 pts.
Tags:
AS/400
CPYF
IBM iSeries
Hi,
I have two files (A,B) with different definitions (Column names,data type,length etc) are different. Both are on same AS/400 system. Now I want to copy all the records from FILEA to FILEB.
What I want to say is, COPY data from one file to another file, where nothing is common in between them. Kindly let me know how to do this.


Software/Hardware used:
iseries

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 15  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • philpl1jb

    the way you describe it, Cpyf isn't going to work..

    For me this would be an SQL or RPG Program

    Cpyf might work though a series of files where structure stays the same but field names change (which allows format *NOCHK) followed by a copy to the final file probably with format *MAP and *DROP

     

    54,020 pointsBadges:
    report
  • HABBIE
    After a long struggle , I came to know that it will not happen through CPYF. Could you please let me know how can i achieve this through RPG/SQL.
    895 pointsBadges:
    report
  • aceofdelts

    Is this a homework assignment or is there a business purpose to merging the data files ?

    I don't need full specs, but a hint at "why" would steer us toward one method or another.

    Yes we can force the data via CPYF, but it'd be useless in many scenarios.

    2,340 pointsBadges:
    report
  • philpl1jb
    Here is a sample:
    INSERT california_authors (au_id, au_lname, au_fname)
    SELECT au_id, au_lname, au_fname
    FROM authors
    WHERE State = 'CA'
    The select statement has to 
    provide the actual data from your old file in
    the sequence called out in the ( fieldlist, ..)
    and each field needs to be formatted to fit your new types/values.
     
     
    54,020 pointsBadges:
    report
  • HABBIE
    Yes, I came across this scenario to merge the tables.Not a home need
    895 pointsBadges:
    report
  • philpl1jb
    -- Not a home need -- I don't understand. 
    54,020 pointsBadges:
    report
  • ToddN2000
    if this is an actual business need to merge 2 non-like files then you should think about redesigning the application. What is the practical need for merging these 2 files? 
    53,145 pointsBadges:
    report
  • HABBIE
    OK. I will explain this.
    I need to fetch all the columns along with their definitions (name,length,datatype etc) from the database & should display on a single table.I acquired this using QMQRY, thing is that on one QMQRY object i cannot give all the columns( 17 columns mostly).So, I have created two QMQRY objects on a single CL program & copied the outputs of these two QMQRY objects into two different tables.Now I need to merge these two tables ,to make the output as a single table.
    Please let me know, if I made any mistake
    895 pointsBadges:
    report
  • philpl1jb
    I don't know why you can't get all the columns you want in QMQRY .. then I've never used it's front end.  If you can't get all the columns you need then you shouldn't use it.  Use SQL to get all of your columns at one time.
    54,020 pointsBadges:
    report
  • ToddN2000
    Forgive me if I'm still not understanding this correctly. You mention that you are trying to save columns and their definitions in 2 separate QMQRY's. Are you looking to combine these 2 definitions to create a new empty data table or a new table with combined data from both tables? If you are looking to combine the data, is there a common field/column to link the data record/rows together? It may be better if you could post your QMQRY and CL code for us to take a look at.
    53,145 pointsBadges:
    report
  • HABBIE
    Yes . I am trying to merge two different tables with data.There is no common fields/columns between these tables.Kindly forgive me if I am wrong. I am new to this technology. 
    Kindly find the code below:
    QMQRY:
    0003.00 &SELECT                                                        
    0004.00 &FIELDS                                                        
    0005.00 &FROM                                                          
    0006.00 &TABLE                                                         
    0007.00 &WHERE                                                         
    0008.00 &COMP                                                          
    0009.00 &VALUE                                                         
            ****************** End of data ******************************** 

    This is my CL program & the error I am facing ,If I mentioned all the columns on a single QMQRY object.

    http://itknowledgeexchange.techtarget.com/itanswers/qmqry-2/

    Thanks 
    895 pointsBadges:
    report
  • aceofdelts

    That makes sense. So a solution is ...

     - Define a data file (via SQL or compiled DDS source) that contains all fields. Best to name them using same names as in your existing 2 files). Note that I assume the 2 files have no common field names ... if so then redefine so field names are unique.

     - CPYF 1st file using MBROPT(*ADD) and FMTOPT(*MAP *DROP)

     - CPYF 2nd file using MBROPT(*UPDADD) and FMTOPT(*MAP *DROP)

    In theory, this should work. I've never actually tried such a scheme.

    2,340 pointsBadges:
    report
  • Ramshivas
    Dear Sirs,
    I have got a similar kind of challenge, where I should merge 5 file data into a single file one among the 5. I need to consider the file gonna host all other files will have different field lengths, new files added and some fields seems got dropped. After this change data should be same or un-altered.
    Kindly provide me your suggestions.

    Thank you,
    Ramesh
    15 pointsBadges:
    report
  • ToddN2000
    You may have to write a program to read a file and map the individual fields to your new output file format. Take a look at the record formats of each file involved and determine the largest value a field/column can hold. Design your new output file around this and then read a file, map the fields to their new output field name, write the record. Repeat this process for each record then move on to the next file and do the same, mapping to the same output file.
    53,145 pointsBadges:
    report
  • TheRealRaven
    First, if you need an answer, we need to see samples of data from the five input files along with your expected output. It's usually easy enough to combine five files into one simply by JOINing the files. We don't know if that's what you want to do.

    Second, it doesn't seem like you have the same problem as this thread. You need to create a question of your own. What you've done is added a comment to someone else's question. The answer to your question will not be appropriate in this thread.

    When you create your own question, be sure to include enough info to make an answer possible.
    18,995 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: