Ошибка sql 01427

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’