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 returned the same error if the dbparm Block=1 is used.)

Resolution

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

1
2