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]

Re: Not able to assign select query &quot;WITH UR&quot; CLUSE TO A VARIABLE

(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]

Re: Not able to assign select query &quot;WITH UR&quot; CLUSE TO A VARIABLE

On May 9, 5:03 pm, Tonkuma <tonk...@fiberb it.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 !!

[Serge Rielau]

Re: Not able to assign select query &quot;WITH UR&quot; CLUSE TO A VARIABLE

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