When the query or sub-select is used in a scalar context, such
as a scalar subquery, SELECT ... INTO, UPDATE ... SET, then
SQL0811N error may be reported if one of the following two
conditions is true:
1) the subquery contains both an ORDER BY and FETCH FIRST 1
ROW ONLY clauses
2) the subquery is correlated and it contains FETCH FIRST 1
ROW ONLY clause
Note: The problem will only happen when the table involved in
the subquery is column organized.
Example 1: Subquery contains ORDER BY and FETCH FIRST 1 ROW ONLY
clauses
$ db2 "create table t1 ( c1 int, c2 int) organize by column";
$ db2 "insert into t1 values(1,1),(2,2)";
$ db2 -td@ -vf sp1.db2
create or replace procedure sp1()
begin
declare i_count integer default 0;
select c1 into i_count
from t1
order by c2
fetch first 1 row only;
end
DB20000I The SQL command completed successfully.
$ db2 "call sp1()"
SQL0811N The result of a scalar fullselect, SELECT INTO
statement, or VALUES
INTO statement is more than one row. SQLSTATE=21000
Example 2: Subquery is correlated and it contains FETCH FIRST 1
ROW ONLY clause
$ db2 "create table t2 ( c1 int, c2 int) organize by column";
$ db2 "insert into t1 values (1,1)"
$ db2 "insert into t1 values (1,1)"
$ db2 "insert into t2 values (1,1)"
$ db2 "update t2 u set c2 = ( select s.c2 from t1 s where
s.c1 = u.c1 fetch first 1 row only )"
SQL0811N The result of a scalar fullselect, SELECT INTO
statement, or VALUES
INTO statement is more than one row. SQLSTATE=21000 |
****************************************************************
* USERS AFFECTED: *
* When the query or sub-select is used in a scalar context, *
* such as a scalar subquery, SELECT ... INTO, UPDATE ... SET. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Cancun Release 10.5.0.4 (also known as Fix *
* Pack 4) or higher. *
**************************************************************** |
