Error 21000 more than one row returned by a subquery used as an expression

Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.

Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'.

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

error: more than one row returned by a subquery used as an expression

Посмотрим, что там с данными не так?

SELECT e.store_id,
       e.rank_id,
       e.last_name || ' ' || e.first_name AS full_name 
  FROM employee e
 WHERE e.rank_id = 'MANAGER'
 ORDER BY e.store_id, e.last_name, e.first_name

В магазине 201 два менеджера, а в магазине 600 — три.

В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.

Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.

В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:

• должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
• использовать агрегатную функцию;
• использовать LIMIT 1 для ограничения количества строк.

Воспользуемся LIMIT 1:

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
         ORDER BY e.last_name,
                  e.first_name,
                  e.middle_name
         LIMIT 1
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg:

SELECT s.store_id,
       s.name,
       (SELECT string_agg (
                  e.last_name || ' ' || e.first_name, '; '
                  ORDER BY e.last_name,
                           e.first_name
               )
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employees
  FROM store s
 ORDER BY s.store_id

Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.

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