Symptom
When connected to Microsoft SQL Server using the OLEDB database driver , and running the following SQL:
select *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1
PowerBuilder will return the following error:
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:
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:
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:
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:
select *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1
to:
select top 1 *, (select (value_2) from table2 where table2.value_1 = table1.value_1) from table1
