Adding a field when using SQL to copy a file on iSeries

50 pts.
Tags:
AS/400
IBM iSeries
SQL
SQL error messages
SQL INSERT

My company is upgrading some applications and I need to copy the data from current files into the new files. In some cases the new files have additional fields. Is there a way with sql to copy the current data and at the same time add either a default value or a running sequentional value to the new fields? SQL will not copy if the number of fields isn't equal:

insert into ccurra select * from ccurra_old where cucutd >= 20140101

produces an SQL0117 error.  Ideally I need the load the additional field with a running sequence number though zero would be acceptable if necessary. 

Any thoughts would be appreciated.



Software/Hardware used:
iSeries 7.1

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: 7  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
  • CharlieBrowne

    You need to specify the individual fields

    INSERT INTO ccurra                                          
    (LMCODE, LMLDES, LMSDES, LMPN, LMTYPE, LMRS, LMCO, LMTQTY)              
     SELECT '16000   ', 'SOFTGLOSS 3', '3-BRUSH', PART#, 'L', 'R', 100, QTY 
     FROM ccurra_old                                              

    41,430 pointsBadges:
    report
  • CLK

    Hi CharlieBrowne,

    You can copy an entire file using the insert into fileA select * from fileB without specifying either fields or values.  All fields will be mapped from fileA to fileB.  My problem is when there are fewer fields in fileA then in fileB - sql doesn't like the mismatch.  And there are upwards of 220 fields in some of these files.

    50 pointsBadges:
    report
  • CharlieBrowne
    Yes, I understand you can copy all fields.
    If I understood your question, you were asking how to do the insert if the fields in both files are not the same and/or you want to insert default values in some of the fields.
    The SQL statement I put in does that exact thing.
    41,430 pointsBadges:
    report
  • philpl1jb

    Charlie Browne's SQL Uses the fields

    PART# which is placed in LMPN

    QTY placed into LMTQTY

    The othe fields LMCODE, LMLDES, LMSDES, LMTYPE, LMRS, LMCO

    Are loaded with the constant values '16000   ', 'SOFTGLOSS 3', '3-BRUSH', 'L', 'R', 100

    INSERT INTO ccurra                                          
    (LMCODE, LMLDES, LMSDES, LMPN, LMTYPE, LMRS, LMCO, LMTQTY)              
     SELECT '16000   ', 'SOFTGLOSS 3', '3-BRUSH', PART#, 'L', 'R', 100, QTY 
     FROM ccurra_old   

    It's certainly the way that I would do it.    

    51,365 pointsBadges:
    report
  • CLK

    Philip1jb,

    I understand listing all of the fields.  However, listing a string of 221 fields for the item master, 240 fields for the customer master and 160 fields for the item balance file (for examples) seemed to me to be a task that I should investigate replacing with an easier and less error prone method than manually keying all the field names. 

    That easier method doesn't appear to involve SQL even though the fields that do exist in both files have the same field names.

    I'd happily accept only loading the fields that match but sql doesn't seem to like that.  It's all or none.

    50 pointsBadges:
    report
  • CharlieBrowne
    You can always use CPYF with FMTOPT(*MAP *DROP) to do the initial build. Then use SQL to put in default values in selected fields or blank out some fields you really did not want to copy.

    To me, how you do it depends on if this is a oneshot deal or if it will be repetitive (with possible changes each time). Generally, machine time is cheaper that personal time. So if I can do something where I need to run 5 statements/programs that .were easier to create vs spending lost of time to create one pretty program, I go with the QAD (quick and dirty).
    41,430 pointsBadges:
    report
  • philpl1jb

    Sorry you need to include all the fields in the field list.  You should be able to use the default keyword for the values .. if they have default values in the table.

    INSERT INTO ccurra                                          
    (LMCODE, LMLDES, LMSDES, LMPN, LMTYPE, LMRS, LMCO, LMTQTY)              
     

    SELECT Default, Default, Default, PART#, Default, Default, Default, QTY 
     FROM ccurra_old   


    I usually go to interactive sql and prompt it to get the field list an number them .. if you have the dds you can cut and past the field list from there.  You can do a dspffd to a file and cut and past the field list from there. 

    From Navigator go to schema - table - columns - cut and past the list from there.

    51,365 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