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

[situ]

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

[Tonkuma]

(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".

[situ]

Thanks u very much , it worked !!

[Serge Rielau]

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