Multiple result sets returned from embedded SQL in RPG
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
Software/Hardware used:
Software/Hardware used:
ASKED:
December 30, 2004 5:41 PM
UPDATED:
January 31, 2005 2:42 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
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.
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
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