Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / October 2006

Tip: Looking for answers? Try searching our database.

Unbelievable SQL0811N on scalar subselect

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
4.spam@mail.ru - 27 Oct 2006 12:35 GMT
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.
Knut Stolze - 27 Oct 2006 13:33 GMT
> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.