Oracle SQL Developer 2.1

В декабре 2009 года вышла новая версия SQL Developer - 2.1

И чудо произошло!
Спустя несколько лет после появления продукта, наконец-то, появилась версия программы, с которой можно работать.

Т.е. с основной задачей консультанта ОЕБС при работе с БД Oracle - выполнение запросов и сохранение результатов - эта версия справляется вполне успешно.

Это вовсе не признание заслуг SQL Developer.
Это признание того факта, что у Oracle появился свой собственный работоспособный продукт, в котором можно  выполнять запросы.

А самый главный его плюс - бесплатное использование - перевешивает все минусы.

Резюме.
SQL Developer 2.1 переведен в опытно-промышленную эксплуатацию.

Анализ данных: DISTINCT vs GROUP BY

При анализе содержимого таблиц часто нужно посмотреть на распределение значений в каком-либо столбце.
Например, чтобы узнать в каких валютах нам выставляют счета поставщики, нужно проанализировать содержимое столбца INVOICE_CURRENCY_CODE таблицы AP_INVOICES_ALL.
Для этого выполняем запрос:

SQL> SELECT   DISTINCT ai.invoice_currency_code
  2    FROM   ap_invoices_all ai;


INVOICE_CURRENC
---------------
RUR
CHF
GBP
EUR
SEK
USD
XDR

Однако это не самое лучшее решение для анализа.
Куда больше информации можно было бы получить переписав запрос так:

  1  SELECT   ai.invoice_currency_code, COUNT(*)
  2    FROM   ap_invoices_all ai
  3*  GROUP BY ROLLUP (ai.invoice_currency_code)
SQL> /


INVOICE_CURRENC   COUNT(*)
--------------- ----------
CHF                     50
EUR                    882
GBP                    153
RUR                 700121
SEK                     45
USD                   5545
XDR                   3641
                    710437

Теперь мы видим не только уникальные коды валют, но и их распределение по строкам, а в конце и общее число записей в таблице(потрясающий эффект от rollup). Да и для Oracle такой запрос совсем не в тягость, тот же объем работы, что и с DISTINCT.

А анализ на этом только начинается.
Увидев распределение значений (особенно когда их много), сразу становится понятно, что для начала нам нужны самые распространенные. Тогда мы добавляем в запрос ORDER BY:

SELECT   ai.invoice_currency_code, COUNT(*)
  FROM   ap_invoices_all ai
 GROUP BY ROLLUP (ai.invoice_currency_code)
 ORDED BY 2 DESC

Если "мелочь" нас не интересует, то отсекаем её при помощи HAVING:

SELECT   ai.invoice_currency_code, COUNT(*)
  FROM   ap_invoices_all ai
 GROUP BY ROLLUP (ai.invoice_currency_code)
 HAVING COUNT(*) > 100


И всего этого мы бы так и не узнали пользуясь обычным DISTINCT.

Нет DISTINCT-у!
Даешь GROUP BY!

P.S. Приведенные результаты запросов - "ненастояшие".

Сторно журналов ГК. Продолжение

В продолжении темы сторнирования журналов ГК.

Постановка задачи.
Получить список "честных" журналов ГК за период.
Что значит честных?
Предположим у нас 5 журналов. И пятый журнал оказался ошибочным (не важно по какой причине). Мы его сторнировали. Т.е. появился шестой журнал. Пятый и шестой - взаимно гасят друг друга и нам более не интересны. А вот первые 4 - это и есть список честных журналов. Из него исключены сторнированные журналы и то, чем их сторнировали.
Как получить такой список?

По идее, нам нужны несторнированные журналы (accrual_rev_status IS NULL). Однако под такое условие попадает и последний шестой журнал, а он - лишний.

Можно добавить условие, что журналы не должы быть сторнирующими (reversed_je_header_id IS NULL). И для нашего примера с шестью журналами этого достаточно. Однако нельзя исключать ситуацию, когда пользователь ошибся выполняя операцию сторнирования и сторнировал не то что нужно. А потом, поняв ошибку, сделал сторно на сторно. Т.е. появился 7-й журнал. И он нам нужен! 5-й и 6-ой гасят друг друга, а 7-й - "настоящий" и он нам нужен.

Здесь сделаем допущение, что из связки 5-6-7 журналы, нам нужен последний - 7-й. Хотя можно было бы говорить и об исходном - 5-ом.

Т.к. сторно на сторно можно делать не единожды, то в общем случае получается такая картина. Если есть цепочка сторнирования: исходный журнал(1)->сторно(2)->сторно(3)->сторно(4)->сторно(5)->сторно(6)-сторно(7)-... то нас в ней интересует последний журнал, но только в том случае, если он нечетный в цепочке.

Результирующий список честных журналов за период получается как то так.

SELECT * /* Обычные несторнированные и несторнирующие журналы */ 
  FROM gl_je_headers gjh 
 WHERE gjh.period_name = 'ЯНВ-2009'
   AND gjh.accrual_rev_status IS NULL
   AND gjh.reversed_je_header_id IS NULL
UNION ALL
SELECT * /* дополнительные сторнировочные журналы */ 
  FROM gl_je_headers gjh 
 WHERE gjh.period_name = 'ЯНВ-2009'
  AND gjh.accrual_rev_status IS NULL
  AND gjh.reversed_je_header_id IS NOT NULL
  AND MOD((SELECT COUNT(*)
             FROM gl_je_headers gjh2
            WHERE gjh.period_name = 'ЯНВ-2009'
            START WITH gjh2.je_header_id = gjh.je_header_id
           CONNECT BY gjh2.je_header_id = PRIOR gjh2.reversed_je_header_id)
   ,2) <> 0  

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

Складские организации. Статус закрытия периодов

Как правило в системе есть несколько операционных единиц (ведь в мелких конторах ОЕБС не внедряется), а в каждой из них несколько складских организаций, причем в разных ORG_ID может быть разное количество складских организаций.

В отличии от Дебиторов/Кредиторов, где период закрывается сразу на всю операционную единицу, в Запасах периоды нужно закрывать в каждой конкретной складской организации.

Во время закрытия очередного периода важно контролировать статусы закрытия в складских организациях.

Так вот оказалось, что наглядную картину можно получить одним запросом (чуть подправив).
А всё благодаря аналитическим функциям.

SELECT t.period_name
       -- Вместо 1,2,3 нужно подставить реальные значения.
       -- полный список складских ORG_ID: 
       -- SELECT DISTINCT operating_unit FROM org_organization_definitions
      ,MAX(DECODE(t.org_id, 1, t.NAME, NULL)) AS ORG_ID_1
      ,MAX(DECODE(t.org_id, 2, t.NAME, NULL)) AS ORG_ID_2
      ,MAX(DECODE(t.org_id, 3, t.NAME, NULL)) AS ORG_ID_3
       -- и так далее, для каждой ORG_ID
  FROM (
        SELECT oap.period_name
              ,ood.operating_unit AS org_id
              ,DECODE(oap.open_flag, 'N','Закрыто', 'Y','Открыто', oap.open_flag) 
               || ' - ' || ood.organization_code||' '||ood.organization_name AS NAME
              ,oap.period_start_date AS start_date
              ,oap.schedule_close_date AS end_date
              ,rank() OVER (
                  PARTITION BY oap.period_name, ood.operating_unit 
                      ORDER BY DECODE(oap.open_flag, 'N','Закрыто', 'Y','Открыто', oap.open_flag) 
                               || ' - ' || ood.organization_code||' '||ood.organization_name
               ) AS row_num
          FROM org_organization_definitions ood
              ,org_acct_periods oap
         WHERE oap.organization_id = ood.organization_id
           -- диапазон дат можно задавать в несколько периодов
           AND oap.period_start_date >= TO_DATE('01.01.2009', 'DD.MM.YYYY')
           AND oap.schedule_close_date <= TO_DATE('31.03.2009', 'DD.MM.YYYY')
           -- можно задать конкретный тип периода (если используется несколько)
           --AND oap.period_set_name = ''
           -- откинем "левые" органзицаии, типа мастер организации позиций и закрытые
           AND ood.organization_code <> '000'
           AND ood.disable_date IS NULL
         ORDER BY 4,5,2,3
  ) t
GROUP BY t.start_date, t.end_date, t.period_name, t.row_num
ORDER BY t.start_date, t.end_date, t.period_name, t.row_num

Внешнее соединение

Человек старой закалки (кто видел СУБД Oracle версии меньше чем 9) на вопрос о внешнем соединении в SQL запросе уверенно ответит, что где то нужно поставить "плюсик".

И это правда.
Не смотря на появившийся в 9-й версии синтаксис ANSI для внешних соединений, "плюсик" привычнее и роднее. Вот, только, не всегда помнишь куда ж его нужно поставить. Собственно далее идет памятка о постановке "плюсика" во внешнем соединении.

Предположим есть у нас две таблицы: FND_USER и HR_EMPLOYEES. Вообще-то, HR_EMPLOYEES это view, но пусть для упрощения немного побудет таблицей. Соединяются они по столбцу EMPLOYEE_ID, который присутствует в обеих. Причем в HR_EMPLOYEES это еще и первичный ключ, а в FND_USER этот столбец не является обязательным.

Соединение этих таблиц в запросе выглядит так:

SELECT fu.*
      ,he.*
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id

А теперь вопрос.
Где же ставить "плюсик"?
Ответ оказывается не однозначным и всё зависит от того, что мы хотим получить в запросе.

Допустим, нам нужно для ряда пользователей (скажем, имя которых начинается с 'S') определить их Фамилию Имя Отчество. Информация о ФИО содержится в столбце HR_EMPLOYEES.full_name
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id(+) = fu.employee_id
   AND fu.user_name LIKE 'S%'

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

Ведь в таблице HR_EMPLOYEES столбец employee_id является первичным ключом, и значение в нем отсутствовать не может в принципе. Однако "плюсик" нужно поставить именно с этой стороны.
В чём же дело.
В данном случае, мы говорим о том, что в указанной постановке задачи (для пользователя найти ФИО) таблица FND_USER "главнее". Мы изначально имеем дело со списком пользователей, для части из которых, нужно найти дополнительную информацию (ФИО), если она существует.
Почему она может не существовать?
Да потому, что у пользователя в таблице FND_USER может отсутствовать значение в столбце EMPLOYEE_ID. Значит для ряда записей в FND_USER (где employee_id IS NULL) в HR_EMPLOYEES можно ничего и не искать. Именно поэтому "плюсик" ставится со стороны таблицы HR_EMPLOYEES

Кстати.
Аналогом этого запроса является:

SELECT fu.user_name
      ,(SELECT he.full_name
          FROM hr_employees he
         WHERE he.employee_id = fu.employee_id)
  FROM fnd_user fu
 WHERE fu.user_name LIKE 'S%'

В таком виде наиболее очевидно, что справочник пользователей "главнее". Ведь FND_USER единственная таблица во фразе FROM. А информация о ФИО вытаскивается подзапросом, который либо её найдет, либо нет.
Основным минусом такого запроса является то, что подзапрос позволяет вернуть только одно значение. А ведь нас могло бы интересовать не только full_name, но и employee_num, creation_date и т.д.

Другая ситуация.
Для ряда сотрудников, у которых фамилия начинается на 'А' нужно найти имена пользователей, с которыми они работают в системе. Имена пользователей содержатся в столбце FND_USER.user_name.
В этом случае запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he
      ,fnd_user fu
 WHERE he.employee_id = fu.employee_id(+)
   AND he.full_name LIKE 'А%'

Запрос вроде похожий, однако "плюсик" переехал к таблице FND_USER.
В данном случае, мы говорим о том, что в указанной постановке задачи (для ряда сотрудников найти user_name) таблица HR_EMPLOYEES "главнее". Мы изначально имеем дело со списком сотрудников, для части из которых, нужно найти дополнительную информацию (user_name), если она существует.
Почему она может не существовать?
Да потому, что для сотрудника из таблицы HR_EMPLOYEES может отсутствовать запись в таблице FND_USER с соответствующим значением столбца EMPLOYEE_ID. Значит для ряда записей в HR_EMPLOYEES мы ничего не найдем в FND_USER. Именно поэтому "плюсик" ставится со стороны таблицы FND_USER.

Однако и это еще не всё.
А что если мы хотим получить полный список и сотрудников, и пользователей зарегистрированных в системе. Теперь мы уже знаем, что для ряда сотрудников могут быть не найдены пользователи, а для ряда пользователей могут быть не найдены сотрудники. Т.е. "плюсик" нужен как бы с обеих сторон. Однако такой синтаксис команды не поддерживается.

Существуют разные способы переписать этот запрос по другому(без внешнего соединения), однако наша цель до конца разобраться именно во внешнем соединении.

Для реализации такого запроса необходимо полное внешнее соединение (FULL OUTER JOIN). И в этом случае придется воспользоваться синтаксисом ANSI.

А выглядеть это будет так:

SELECT fu.user_name
      ,he.full_name
  FROM hr_employees he FULL OUTER JOIN fnd_user fu
    ON he.employee_id = fu.employee_id

Подводя итоги, можно сказать следующее.
Если речь не идет о полном внешнем соединении, то нужно понять какая из таблиц является главной, а какая дополняющей. "Плюсик" в условии соединения таблиц (фраза WHERE) ставим со стороны дополняющей таблицы.

Ну и напоследок, чтобы окончательно запутаться.
А что если в запросе больше чем две таблицы? Допустим нам нужно для текущего пользователя (функция FND_GLOBAL.user_id) найти номер рабочего телефона сотрудника.

Информация о телефонах хранится в таблице PER_PHONES. Эта таблица связывается с HR_EMPLOYEES по условию:

   per_phones.parent_table = 'PER_ALL_PEOPLE_F'
   AND per_phones.parent_id = hr_employees.employee_id

Но при этом очевидно, что не у всех сотрудников есть записи о телефонах.

Итоговый запрос будет выглядеть так:

SELECT fu.user_name
      ,he.full_name
      ,pp.phone_number
  FROM fnd_user fu
      ,hr_employees he
      ,per_phones pp
 WHERE /* Соединяем fnd_user и hr_employees */
       he.employee_id(+) = fu.employee_id
       /* Соединяем hr_employees и per_phones */
   AND pp.parent_id(+) = he.employee_id
   AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
       /* Прочие ограничения */
   AND pp.phone_type(+) = 'W1' -- рабочий телефон
   AND SYSDATE BETWEEN pp.date_from(+) AND NVL(pp.date_to(+), SYSDATE) -- актуальная запись
   AND fu.user_id = FND_GLOBAL.user_id -- для текущего пользователя

Отметим следующее.
Таблицы соединяем попарно.
В каждой паре определяем главную и дополняющую таблицу.
В паре fnd_user и hr_employees главная - fnd_user, поэтому "плюсик" со стороны hr_employees.
В паре hr_employees и per_phones главная hr_employees, поэтому "плюсик" ставится со стороны per_phones и что особенно важно "плюсик" не ставится со стороны hr_employees.
В прочих условиях "плюсик" ставится для тех таблиц, которые хоть где-нибудь были дополнительными и не ставится у главной таблицы запроса.

ORA-01427: подзапрос одиночной строки возвращает более одной строки

Приходилось сталкиваться с такой ошибкой?
Читаем дальше.

Зачем же использовать такие подзапросы, коль возможны ошибки?
Но ведь удобно же!

Пример.
Ест у нас запрос, который скажем выводит некий список расходных транзакций модуля 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'