469,608 Members | 1,785 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,608 developers. It's quick & easy.

Assign multiple variables from select statement in Procedure

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
1 6477
> 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.

Similar topics

2 posts views Thread by Richard Adams | last post: by
12 posts views Thread by Dennis D. | last post: by
2 posts views Thread by Diego | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.