Приходилось сталкиваться с такой ошибкой?
Читаем дальше.
Зачем же использовать такие подзапросы, коль возможны ошибки?
Но ведь удобно же!
Пример.
Ест у нас запрос, который скажем выводит некий список расходных транзакций модуля 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'
Сообщения
