Hello.
v8.2.1
Anybody can try this:
----------
--base table
DECLARE GLOBAL TEMPORARY TABLE SESSION.T (I INT)
with replace on commit preserve rows;
--insert 10 rows into table t
INSERT INTO SESSION.T
with t(v) as
(
VALUES (1) union all select v+1 from t where v<10
)
select * from t;
--reference
DECLARE GLOBAL TEMPORARY TABLE SESSION.R
(IND INT GENERATED ALWAYS AS IDENTITY, I INT)
with replace on commit preserve rows;
--insert 10 rows into table r
INSERT INTO SESSION.R (I)
with t(v) as
(
VALUES (1) union all select v+1 from t where v<10
)
select * from t;
--try to update table t
--by random value from reference
update SESSION.T
set I=
(
select IND from session.r where IND=1+round(rand()*(10-1), 0)
);
----------
After last statement I get SQL0811N.
But it's impossible I think because SESSION.R.IND is GENERATED ALWAYS
AS IDENTITY.
How it could be?
Sincerely,
Mark B.
> update SESSION.T
> set I=
[quoted text clipped - 6 lines]
> AS IDENTITY.
> How it could be?
Simple:
one row (IND = 4) in SESSION.R
WHERE ind = 1 + ROUND(RAND() * 9, 0 )
-> RAND() = 3
-> WHERE 4 = 1 + 3
-> true; row qualifies
some other row (IND = 2) in SESSION.R
WHERE ind = 1 + ROUND(RAND() * 9, 0 )
-> RAND() = 1
-> WHERE 2 = 1 + 1
-> true; row qualifies
The thing is that the RAND() function is evaluated for each row in
SESSION.R. You don't reuse the same randomized value for all the rows.
Thus, multiple rows can satisfy the subselect, making it non-scalar.

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
4.spam@mail.ru - 27 Oct 2006 13:59 GMT
I've understood.
Thanks, Knut.
> Simple:
>
[quoted text clipped - 22 lines]
> DB2 Information Integration Development
> IBM Germany