How to update in SQL using DS

85 pts.
Tags:
RPGLE
SQL
My problem is that I’m basically doing an interactive update/create file (similar to DFU). The user is able to maintain a file in the screen, so how do I go about updating the ENTIRE File without having to manually update every record? I have a datastructure (cpRec) to put in the info from the file using SQL D cpRec e ds Extname(CUSPRF) Prefix(cp) If my screen fields are the exact same fields as the file (except with a sc prefix), how can I update the file with the screen fields without doing CustNo = :scCustNo, OrderNo = :scOrderNo, Locations = :scLocation…and so on… I thought I could just use another DS for the screen fields D scRec e ds Extname(CUSPRF) Prefix(sc) and then somehow move that into the file: Exec SQL UPDATE cusprf Set Row = :scRec but when I run it I get a data decimal error on the dspf.

Answer Wiki

Thanks. We'll let you know when a new response is added.

When doing updates in SQL from an outside source you have to do the update commands one command at a time. If you want to do them in batch, create a temp table, load the file contence into the temp table and then do an update using the temp table as the FROM.

Discuss This Question: 9  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
  • MMAGuy
    Maybe I'm not explaining the problem... My screen fields are coming from a display file: fDisplayFile CF E WORKSTN PREFIX(sc) every screen field will match file CUSPRF.... I have a data structure defined as: D cpRec e ds Extname(CUSPRF) Prefix(cp) If I use SQL and load cpRec with the values from CUSPRF, how do I "fill in" the screen fields with the values without doing the following? scMonth = cpMonth; scYear = cpYear; scBegInv = cpBegInv; scSkuQty = cpSkuQty; and so one for the entire file fields. I want to use a data structure, but I can't name the data structure fields the same as the display file or I get a data decimal error when running the program screen fields will all have a sc prefix (example scCustomerName) so how do I move cpCustomerName into scCustomerName...as well as every other field without listing every field? This doesn't work: D ScreenDS e ds Extname(CUSPRF) Prefix(sc) ScreenDS = cpRec; Thanks
    85 pointsBadges:
    report
  • Unknowingone
    What is causing the data decimal error?
    195 pointsBadges:
    report
  • kevleemor
    Usually a decimal data error results from moving a blank or alpha value into a numeric field. In this situation I normally verify all the numeric field operations to ensure I'm not tryng to put a alpha field into it. Then I run the program in debug mode and check the values of the field just at the point the error message is being returned. Kevin
    605 pointsBadges:
    report
  • MMAGuy
    The data decimal error occurs when the display file is opened. When put into debug it crashed on: fCUSPRFMTFMCF E WORKSTN PREFIX(sc)
    85 pointsBadges:
    report
  • kevleemor
    Did you check the datastructure fields to ensure the numeric fields have valid data before the display file is opened? If that is the problem you should initialize the datastructure before it is used. Kevin
    605 pointsBadges:
    report
  • MMAGuy
    Thanks Kevleemor! That was it...I put the INZ in the d-specs (for the DS) and it worked. Thanks a bunch.
    85 pointsBadges:
    report
  • kevleemor
    I'm glad it worked! Have a great 2008!
    605 pointsBadges:
    report
  • MMAGuy
    Now that I have that working, how to I update a file in that same way? I tried the following and it didn't work: Exec SQL UPDATE cusprf Set Row = :scDS WHERE CustNo = :scCustNo AND Month = :scMonth AND Year = :scYear; Thanks
    85 pointsBadges:
    report
  • kevleemor
    From what I know I'm not sure that you can do that! In order to update an entire row in a table you must use a subselect that returns enough values to fill every field. IE. UPDATE MyFileA SET ROW = (SELECT * From MyFileB WHERE MyField = "MyValue") This SQL statement will update every field in the MyFileA record with the values returned from the MyFileB select statement. ...or you can explicitly nominate the field/column (s) you want to update in the row. e.g. UPDATE MyFileA SET MyFieldA = [MyFieldA Value], MyFieldB = [MyFieldB Value]...WHERE [Conditional] Have fun!
    605 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