469,282 Members | 1,917 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Not able to assign select query "WITH UR" CLUSE TO A VARIABLE

Hi,
i'm using Db2 Version 8.2.

in a stored procedure i'm assiging a the result of sql query ( with
"WITH UR" ) to a variable as shown below.
SET v_temp = ( SELECT 1 FROM
Table_1 A , Table_2 B
WHERE a.cd=b.cd )
WITH UR;

But i'm getting the fallowing syntax error.

An unexpected token "WITH UR" was found following " ". Expected
tokens may include: "<space>". LINE NUMBER=91. SQLSTATE=42601

i even tried to put that cluase inside the bracket but no luck.

can any body please suggest be the work around for this.

Thanks,
Situ
Jun 27 '08 #1
3 4630
(I didn't tested following opinion.
I was only refered the manuals
"DB2 for LUW SQL Reference Volume 1 Version 8" and
"DB2 for LUW SQL Reference Volume 2 Version 8".)

1) Your SET statement violates the following syntax of SET statement
and "fullselect".

SET (variable1, variable2, ..., variablen) = (row-fullselect);
or
SET variable = expression;
"expression" includes (scalar-fullselect).

You can't specify "isolation-clause" in a "fullselect".

Please try:
SELECT 1 INTO v_temp
FROM Table_1 A , Table_2 B
WHERE a.cd = b.cd
WITH UR;

2) I afraid that the SELECT statement may return multiple rows, and
get error.
If there is such possibility, it would be better to add "FETCH FIRST 1
ROW ONLY".
Jun 27 '08 #2
On May 9, 5:03*pm, Tonkuma <tonk...@fiberbit.netwrote:
(I didn't tested following opinion.
*I was only refered the manuals
*"DB2 for LUW SQL Reference Volume 1 Version 8" and
*"DB2 for LUW SQL Reference Volume 2 Version 8".)

1) Your SET statement violates the following syntax of SET statement
and "fullselect".

SET (variable1, variable2, ..., variablen) = (row-fullselect);
or
SET variable = expression;
"expression" includes (scalar-fullselect).

You can't specify "isolation-clause" in a "fullselect".

Please try:
SELECT 1 INTO v_temp
* FROM Table_1 A , Table_2 B
*WHERE a.cd = b.cd
* WITH UR;

2) I afraid that the SELECT statement may return multiple rows, and
get error.
If there is such possibility, it would be better to add "FETCH FIRST 1
ROW ONLY".
Thanks u very much , it worked !!
Jun 27 '08 #3
situ wrote:
Hi,
i'm using Db2 Version 8.2.

in a stored procedure i'm assiging a the result of sql query ( with
"WITH UR" ) to a variable as shown below.
SET v_temp = ( SELECT 1 FROM
Table_1 A , Table_2 B
WHERE a.cd=b.cd )
WITH UR;

But i'm getting the fallowing syntax error.
The SET statement does not support the isolation clause
i even tried to put that clause inside the bracket but no luck.
That would be isolation clause in a subquery, presently not supported
can any body please suggest be the work around for this.
Try SELECT INTO, or using a (one row) cursor

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John Gibson | last post: by
2 posts views Thread by Griff | last post: by
5 posts views Thread by Alexander Myodov | last post: by
10 posts views Thread by tsmithnj | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.