Multiple result sets returned from embedded SQL in RPG

0 pts.
Tags:
RPG
SQL
I am trying to follow various examples (examples from Iseries Network and IBM manuals) for declaring a cursor and fetching records. I have no problem with simple statements. I am having trouble converting a statement containing a subquery. The more I look at different examples, the more trouble I am having. Can you help? This statement updates the work subscription file with the product, member id, and acquisition channel from the supplemental subscription file where the products, ids and campaign codes match in both files C exec sql C+ UPDATE mylib/workfile C+ Set s2prod = C+ (SELECT s2prod from sub2l12 WHERE workfile.sprod = sub2l12.s2prod and C+ and workfile.sid = sub2l12.s2id and workfile.smail = sub2l12.s2mail), C+ s2id = C+ (SELECT s2id from sub2l12 WHERE workfile.sprod = sub2l12.s2prod C+ and workfile.sid = sub2l12.s2id and workfile.smail = sub2l12.s2mail), C+ s2ach = C+ (SELECT s2ach from sub2l12 WHERE workfile.sprod = sub2l12.s2prod C+ and workfile.sid = sub2l12.s2id and workfile.smail = sub2l12.s2mail) C end-exec Thank you for any help you can provide
ASKED: December 30, 2004  5:41 PM
UPDATED: January 31, 2005  2:42 PM

Answer Wiki

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

Hi to all,
I don’t understand very exactly your question,
because you refer to an UPDATE statement and, as per my
knowledge, for update statements you don’t need neither to
declare any cursor nor to fetch records from it.
You simply need to execute the SQL command.
Depending on your host environment, executing the command
may return the total number of records processed (in your
case the total number of records updated).

As my background ts Oracle, the update statement in your example would be rewritten as follows for more efficient
execution, of course, if your database syntax supports
it:

exec sql
UPDATE mylib/workfile
Set ( s2prod, s2id, s2ach ) =
(SELECT s2prod, s2id, s2ach
from sub2l12
WHERE workfile.sprod = sub2l12.s2prod
and workfile.sid = sub2l12.s2id
and workfile.smail = sub2l12.s2mail)
end-exec

and this is because the 3 subquesries used for the 3 fields
are in fact identical.

Hope this helps,
Best Rgds,
Iudith

Discuss This Question: 5  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
  • RolandT
    Not sure that I understand your question, but if your getting an error regarding multiple results returned, I think that the system is indicating that one or more sub-selects is returning multiple records. This is not allowed in a sub-select for an update statement.
    0 pointsBadges:
    report
  • Qpgmrs
    Thanks Welcome and RolandT! That is a better statement Welcome. Roland T you are correct. I am receiving multiple result sets. I actually want to process all results. That is why I need to set a cursor and fetch each record. With that subselect in there, I am having trouble figuring out how to write the statement to set the cursor.
    0 pointsBadges:
    report
  • RolandT
    You can't have a cursor associated with an update statement. You need to setup a select with a cursor, then run an update against the current of the cursor.
    0 pointsBadges:
    report
  • Rchevalier
    You have two separate issues, both previously mentioned but maybe this will help clarify them. First, you can't assign a cursor to an update statement. It isn't returning a set of records. Second, and the actual question you asked I think, is your sub-selects. You are trying to compare a field value to the result of a select. This can be done ONLY if the select returns one, and only one, value (row). In your case it sounds like the sub-select is returning multiple rows. SQL can't compare the single field value to multiple values (rows) from your sub-select. You can try changing the '=' to 'in'. If that doesn't work you will need to either change the sub-select to return only a single value or assign a cursor to your sub-select and process each returned value. HTH
    0 pointsBadges:
    report
  • Teamsi
    Certainly, the sub-select in the UPDATE clause is looking to get back ONE row; so then the question becomes what is exactly returning from the sub-select? Are ALL the values returning from the sub-select (for any given product, id and campaign) the SAME, if so you can simply use the "DISTINCT" function in your sub-select to return back only ONE row. If you actually have multiple values returning for one given product, id and campaign then you may need to apply more logic to further isoloate the single correct return value. The update clause doesn't know which of the returned values to use to complete the operation and is giving you an error for it. Good luck
    0 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