Приходилось сталкиваться с такой ошибкой?
Читаем дальше.
Зачем же использовать такие подзапросы, коль возможны ошибки?
Но ведь удобно же!
Пример.
Ест у нас запрос, который скажем выводит некий список расходных транзакций модуля Inventory
SELECT ...
FROM mtl_material_transactions mmt
WHERE ...
Нам здесь не важно, что выводит этот список и по какому критерию, но нужно отметить, что за многоточиями может скрываться не один десяток, а то и не одна сотня, строк кода.
Но вот возникла необходимость добавить в запрос еще одну колонку — счет ГК с которого списали ТМЦ. Мы знаем, что в таблице mtl_transaction_accounts по коду складской транзакции можно найти две полупроводки, одна с положительной суммой (дебет), другая с отрицательной (кредит). Ну вот значит счет кредитовой полупроводки нас и интересует. Самым простым способом «вклиниться» в существующий запрос будет что-то такое:
SELECT ...
,(SELECT mta.reference_account
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = mmt.transaction_id
AND mta.base_transaction_value < 0
) AS "Счет учета ТМЦ"
FROM mtl_material_transactions mmt
WHERE ...
Запускаем — беда!
ORA-01427: подзапрос одиночной строки возвращает более одной строки
Почему возникла ошибка?
Для некоторых складских транзакций наши предположения оказались неверны.
И это плохо.
А плохо потому, что проблемными могут оказаться всего лишь пара записей из нескольких тысяч, а мы не увидим ничего, т.к. весь запрос возвращает ошибку.
Прежде чем начинать исправить ситуацию, нужно понять а какой собственно результат запроса был бы приемлемым, учитывая наличие складских транзакций с неожиданными распределениями(проводками)?
А хотелось бы, чтобы запрос таки отработал, и все сотни, а то и тысячи (а то и больше) «правильных» записей мы увидели, а для тех нескольких ошибочных пусть вернется хоть что-нибудь — мы с ними отдельно разберемся, главное чтобы их отличить от правильных можно было.
Теперь приступаем.
Итак для того чтобы ошибка не возникала, нужно чтобы там где подзапрос возвращает несколько записей — возвращалась одна.
Одна запись из нескольких может получиться при использовании групповых функций.
Ну что же:
SELECT ...
,(SELECT MAX(mta.reference_account)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = mmt.transaction_id
AND mta.base_transaction_value < 0
) AS "Счет учета ТМЦ"
FROM mtl_material_transactions mmt
WHERE ...
Однако. Как оказалось, вернуть что-нибудь — не проблема, проблема потом понять что получили. Применив групповую функцию MAX мы гарантируем, что ошибки ORA-01427 больше не будет. Какой-нибудь счет да вернется. Но при таком подходе, мы никогда и не узнаем, что у нас есть записи с некорректно
определенным счетом.
Тем не менее, главный шаг к правильному решению уже сделан, осталось чуть-чуть. Ведь во всех случаях, где подзапрос возвращает одну запись, использование MAX не является ошибкой — максисум от одного значения равен самому значению. Значит нам нужно в тех случаях, где подзапрос возвращает одну запись — использовать
MAX (ну хотите MIN). А там где больше чем одну — возвращать значение, указывающее на ошибку.
Так ведь это же совсем не сложно сделать!
Количество записей подзапроса — это COUNT(*), условную логику можно реализовать через CASE или, по старинке, через DECODE. Не забудем и про то, что подзапрос может совсем не вернуть записей:
SELECT ...
,(SELECT DECODE(COUNT(*), 1,MAX(mta.reference_account), 0,NULL, -999)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = mmt.transaction_id
AND mta.base_transaction_value < 0
) AS "Счет учета ТМЦ"
FROM mtl_material_transactions mmt
WHERE ...
Всё.
Теперь не только ошибка ORA-01427 больше не появится, но и можно легко найти те записи, где наша логика определения счета учета ТМЦ дала сбой.
Дополнительно отметим, что так как mta.reference_account имеет числовой тип данных, то и ошибочное значение должно быть числовым (-999). Для строковых типов данных можно было бы использовать — ‘Ошибка’ или ‘ORA-01427’. Для дат — что-то из далекого прошлого или будущего. Важно лишь, чтобы такого значения гарантированно не было в реальных данных.
Подводим итоги.
При использовании подзапросов вместо
SELECT
(SELECT t2.column
FROM table2
WHERE ...)
FROM table1 t1
WHERE ...
лучше использовать
SELECT
(SELECT DECODE(COUNT(*), 0,NULL, 1,MAX(t2.column), 'ORA-01427')
FROM table2
WHERE ...)
FROM table1 t1
WHERE ...
И не забыть разобраться почему появились записи с ‘ORA-01427’
Use the following query:
SELECT E.I_EmpID AS EMPID,
E.I_EMPCODE AS EMPCODE,
E.I_EmpName AS EMPNAME,
REPLACE(TO_CHAR(A.I_REQDATE, 'DD-Mon-YYYY'), ' ', '') AS FROMDATE,
REPLACE(TO_CHAR(A.I_ENDDATE, 'DD-Mon-YYYY'), ' ', '') AS TODATE,
TO_CHAR(NOD) AS NOD,
DECODE(A.I_DURATION,
'FD',
'FullDay',
'FN',
'ForeNoon',
'AN',
'AfterNoon') AS DURATION,
L.I_LeaveType AS LEAVETYPE,
REPLACE(TO_CHAR((SELECT max(C.I_WORKDATE)
FROM T_COMPENSATION C
WHERE C.I_COMPENSATEDDATE = A.I_REQDATE
AND C.I_EMPID = A.I_EMPID),
'DD-Mon-YYYY'),
' ',
'') AS WORKDATE,
A.I_REASON AS REASON,
AP.I_REJECTREASON AS REJECTREASON
FROM T_LEAVEAPPLY A
INNER JOIN T_EMPLOYEE_MS E
ON A.I_EMPID = E.I_EmpID
AND UPPER(E.I_IsActive) = 'YES'
AND A.I_STATUS = '1'
INNER JOIN T_LeaveType_MS L
ON A.I_LEAVETYPEID = L.I_LEAVETYPEID
LEFT OUTER JOIN T_APPROVAL AP
ON A.I_REQDATE = AP.I_REQDATE
AND A.I_EMPID = AP.I_EMPID
AND AP.I_APPROVALSTATUS = '1'
WHERE E.I_EMPID <> '22'
ORDER BY A.I_REQDATE DESC
The trick is to force the inner query return only one record by adding an aggregate function (I have used max() here). This will work perfectly as far as the query is concerned, but, honestly, OP should investigate why the inner query is returning multiple records by examining the data. Are these multiple records really relevant business wise?
ORA-01427
ORA-01427: запрос к единственной строке возвращает более чем одну строку
Причина:
Так как ваш подзапрос возвращает более чем одну строку, ваш внешний запрос должен использовать одно из ключевых слов ANY, ALL, IN, или NOT IN для указания значений для сравнения.
Действие:
Используйте ANY, ALL, IN или NOT IN для указания какие значения надо сравнивать, или сформулируйте запрос так, чтобы находилась одна строка.
There’re two error patterns related to ORA-01427 described in this post:
- SELECT with Equal Operator
- Job Failed by Trigger.
I will talk about them respectively in the following sections. In which, the first error pattern is very common and easy to solve. But the second one is not so obvious, you need more patience to solve it.
A. ORA-01427 in SELECT with Equal Operator
If SQL engine expects your subquery to return a single row, it may throw ORA-01427 when the subquery returns more than one row unexpectedly. For example:
SQL> select * from employees where department_id = (select department_id from departments where location_id = 1700);
select * from employees where department_id = (select department_id from departments where location_id = 1700)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.
Solutions
It’s just like that we wanted strictly only one item, but eventually it returned more than we expected.
Equal Sign Wants Only One. But Get More Eventually!
1. Using IN Operator
Then what operator is used to prevent ORA-01427 in SELECT statement? In practice, SELECT should use IN operator instead of = (equal operator) in order to accommodate more than one row returned by the subquery.
SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);
A similar exception that relates to returned number mismatch is ORA-00913: too many values. But they have different error patterns.
More comparison conditions like ANY, SOME or ALL should also be helpful to accept more than one row in your statements so as to avoid ORA-01427 in subquery statement.
2. Using = (Equal) Operator
If you really want to use an equal operator to confine the scope of returned rows as one, you have to limit the number of rows returned of queries to only one row. That’s how we workaround it.
SQL> select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700) where rownum = 1);
An order by clause is more appropriate in the above subquery so as to be close to your expectation.
select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700 order by manager_id) where rownum = 1);
3. Do Not Use DISTINCT with Equal Operator
Adding one distinct keyword before column list cannot prevent ORA-01427, because the number of rows returned by the subquery is still unpredictable.
SQL> select * from employees where department_id = (select distinct department_id from departments where location_id = 1700);
select * from employees where department_id = (select distinct department_id from departments where location_id = 1700)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
No surprises, we saw ORA-01427 in SELECT statement once again.
B. ORA-01427 in Job Failed by Trigger
ORA-12012 and ORA-01427
Same error ORA-01427 could accompany ORA-12012 in the alert log when one scheduled job failed to complete successfully.
ORA-12012: error on auto execute of job 10
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 21
Trigger Caused ORA-01427
According to this error pattern in the above, we saw ORA-01427 in a failed job with ORA-12012. We’d better check some triggers, especially logon and logoff ones to see if there’s any chances to block the job process accidentally. Noticeably, which is Job No. 10 in this case.
You should check the content of the job in the first place to see if there’re any chances to throw ORA-01427. Perhaps you should disable each trigger at a time in order to isolate and identify the cause. Or just turn them off temporarily.
Further reading: How to Kill Session in Logon Trigger
C. MySQL Subquery Returns More Than 1 Row
The same error pattern occurs in MySQL, incorrect number of rows from subquery will result ERROR 1241, let’s see its content:
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = «Subquery returns more than 1 row»
This error is caused by the subquery that must return at most one row but returns multiple rows eventually.
mysql> select * from items where id = (select item_id from orders);
If the subquery returns just one row, the above query will work without errors. If it returns more than one row, we will see error 1242.
The solution is the same as we mentioned in the above sections. Beside IN operator, we can also use ANY, one of comparison conditions to fix the query, for example:
mysql> select * from items where id = any (select item_id from orders);
This is how comparison conditions work for MySQL.
I am trying to update a column based on another column in the same table (student table) and a column
from another table (school table)
Code is:
update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)
I get the following error
ORA — 01427 Single-row subquery returns more than one row
Any help would be appreciated.
Burhan Ali
2,2541 gold badge26 silver badges38 bronze badges
asked Apr 2, 2012 at 3:14
If you run your subquery you’ll find it returning more than one row. You are trying to update a column to be equal to the result of your subquery so it expects only one value. You should limit your subquery to only return one row such as using max() or min() or, perhaps you meant to join to the outer student_table? Try:
update student_table n
set student_code =
(select l.student_code
from school_table l
where l.school = n.schoolname);
answered Apr 2, 2012 at 3:24
John DoyleJohn Doyle
7,4055 gold badges33 silver badges40 bronze badges
1
It would be helpful to have a plain English explanation of what you are trying to accomplish. Having said that, it appears to me that you can accomplish what you want to do with the following SQL [assuming one to many relationship between school_table and student_table] having the inner select as a corelated sub-query with the outer update statement:
update student_table
set student_code = (select l.student_code
from school_table
where school_table.school = student_table.schoolname)
;
Hope this helps.
Regards,
Roger
answered Apr 3, 2012 at 14:15
Roger CornejoRoger Cornejo
1,5171 gold badge8 silver badges7 bronze badges
0
We all know exactly what the error says. SET only expects one value per column to be set. What we want to achieve is Update all rows for a given column using values from another table’s column.
Now here’s the solution:
BEGIN
For i in (select col_X, col_Y from table1)
LOOP
Update table2 set col1 = i.col_X where col2 = i.col_Y;
END LOOP;
END;
That’s how exactly you run it on SQLDeveloper worksheet. They say it’s slow but that’s the only solution that worked for me on this case.
answered Jun 27, 2013 at 2:54
Pau KarrPau Karr
2552 silver badges7 bronze badges
your inner query..
select l.student_code
from school_table l, student_table n
where l.school = n.schoolname
might return more than one value. Run the inner query and check the number of the value.
answered Apr 2, 2012 at 3:29
chinna_82chinna_82
6,33317 gold badges78 silver badges134 bronze badges
2
restrict the output of the inner query to one value to successfully run your query.
select l.student_code
from school_table l, student_table n
where l.school = n.schoolname
check this
answered Apr 2, 2012 at 6:36
Try to add and rownum=1 to your subquery conditions if you DO NOT care about the value from the list or DO sure that they are the same.
answered Dec 16, 2014 at 8:49