CPYF Command in AS/400

810 pts.
Tags:
AS/400
CPYF
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 12  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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

     

    49,590 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.
    810 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.

    1,850 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.
     
     
    49,590 pointsBadges:
    report
  • HABBIE
    Yes, I came across this scenario to merge the tables.Not a home need
    810 pointsBadges:
    report
  • philpl1jb
    -- Not a home need -- I don't understand. 
    49,590 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? 
    8,560 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
    810 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.
    49,590 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.
    8,560 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 
    810 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.

    1,850 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following