SEO

#1 (**permalink**) 05-10-2008, 02:03 PM

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

#2 (**permalink**) 05-10-2008, 02:03 PM

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

#3 (**permalink**) 05-10-2008, 02:03 PM

On May 9, 5:03*pm, Tonkuma <tonk...@fiberbit.net> wrote:
> (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 !!

#4 (**permalink**) 05-10-2008, 02:03 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

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