Getting a SQLSTATE=21000 error when running SQL using the OLEDB
database driver
Symptom
When connected to Microsoft SQL Server using the OLEDB database
driver , and running the following SQL:
|
1 |
select *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1 |
PowerBuilder will return the following error:
|
1 |
Select Error: SQLState=21000 |
Subquery returned more than 1 value. This is not permitted when
the subquery follows =, !=, < , , < =, > , > = or when the
subquery is used as an expression
When connected to Microsoft SQL Server using SNC, a resultset will
be returned. No error is returned.
Environment
PowerBuilder
Microsoft SQL Server
Reproducing the Issue
1. In PowerBuilder, create a new database profile for OLEDB
connecting to Microsoft SQL Server.
2. Run the following SQL:
|
1 2 3 4 5 6 7 8 9 |
create table dbo.table1 (tbl1_ident int identity, value_1 varchar(20)); create table dbo.table2 (tbl2_ident int identity, value_1 varchar(20), value_2 varchar(1)); insert into dbo.table1 (value_1) values ('row_1') ; insert into dbo.table1 (value_1) values ('row_2'); insert into table2 (value_1, value_2) values ('row_1', '1'); insert into table2 (value_1, value_2) values ('row_2', '2'); insert into table2 (value_1, value_2) values ('row_2', '3'); insert into table2 (value_1, value_2) values ('row_2', '4'); insert into table2 (value_1, value_2) values ('row_2', '5'); |
3. Open an existing workspace and target.
4. Create a new tabular DataWindow using the following SQL:
|
1 |
select *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1 |
5. Retrieve the DataWindow and you will see the following error
when connected to OLEDB:
|
1 |
Select Error: SQLState=21000 |
Subquery returned more than 1 value. This is not permitted when
the subquery follows =, !=, < , , < =, > , > = or when the
subquery is used as an expression
6. Now connect using the SNC database profile and retrieve the
same DataWindow and you should see a result set.
Cause
The PowerBuilder OLEDB driver and SNC driver fetch a different
number of rows each time.
(The SNC database driver would return the same error if the dbparm
Block=1 is used.)
Solution
For OLEDB, change the SQL from:
|
1 |
select *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1 |
to:
|
1 |
select top 1 *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1 |