By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,843 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,843 IT Pros & Developers. It's quick & easy.

Assign multiple variables from select statement in Procedure

P: n/a
I am using DB2 V8 fixpack 10 and have the following issue:
From a User Defined Function, I can assign multiple variables in a

single statement as follows:

-- Get the first update transaction for the mailing event.

set (fUpdateTS, fAddrType, fAccountID, fOpCode) =
(select AddTS, UpdateTypeCode, AccountID, OpCode
FROM
(select AddTS, UpdateTypeCode, AccountID, OpCode,
row_number() over(order by AddTS) as RNUM
from CANSAccountUpdates c
where c.MailingEventID = pMailingEventID
and c.UpdateTypeCode IN('LEGAL', 'JLEGL', 'ML',
'JML')
) as tmp
WHERE RNUM = 1
);

but when I try to use the same statement in a stored procedure, I get
the error when trying to compile:

SQL0104N An unexpected token "set" was found following " ". Expected
tokens may include <psm_while>. Line Number=78. SQLSTATE=42601.

Is there different way to assign multiple variable from SQL statement
in a DB2 stored procedure?

Many thanks,

Bob

Jun 29 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> SQL0104N An unexpected token "set" was found following " ". Expected
tokens may include <psm_while>. Line Number=78. SQLSTATE=42601.

Is there different way to assign multiple variable from SQL statement
in a DB2 stored procedure?


Multi-target SET isnt currently supported in SQL stored procedures. Use
VALUES INTO or SELECT INTO instead.

Note that its not that bad to issue a series of single SET statements
in a SQL stored procedure - DB2 will often internally rewrite a series
of SET statements into a single VALUES INTO statement.

jsoh

Jun 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.