ORA-00907 Ошибка при использовании аналитической функции в запросе (PS/Query, Peopletools 8.51.12)
Запрос выдает ошибку ORA-00907 при попытке вставить список значений в критерии.
Предыстория: я не разработчик, я просто конечный пользователь, который достаточно изучен, чтобы писать запросы с использованием PS/Query в Peoplesoft для реализации моей компании. Я работаю с FSCM-модулем Peoplesoft ("Финансы и управление цепочками поставок"), в настоящее время работающим с версией FSCM 8.90.08.024, используя, как мне кажется, Oracle 11g в качестве базовой базы данных.
Я в основном самоучка, а наши технические специалисты заняты работой с базами данных / приложениями или не знакомы с конкретными потребностями моего раздела в данных.
Я должен отметить, что я не могу напрямую писать операторы SQL для запроса базы данных. Я должен использовать встроенную программу под названием "PS/Query" (также известный как Query Manager) с графическим интерфейсом, который пишет для вас SQL и сохраняет его как запрос, который вы можете запустить в базу данных для извлечения данных. Это относится к моему вопросу только в том, что:
1. I cannot create or alter views/tables 2. I cannot perform any type of SQL Statement except "SELECT" 3. I can embed PL/SQL, MetaSQL and plain SQL into Expressions 4. At this point, Query Manager is the only option I have.PS/Query - мой единственный опыт работы с SQL, кроме документации Oracle и подобных сайтов. По моим исследованиям, "настоящие" программисты SQL считают его чрезвычайно ограниченным. Ограничения на него требуют от вас действий, которые нарушают то, что, по-видимому, является наилучшей практикой кодирования SQL.
Query Request: у меня есть запрос, который мне было предложено написать, который выводит расходы (на ваучеры и PO) на определенные системные коды категорий. То, что я пытаюсь сделать, это получить идентификаторы ваучеров, суммировать суммы товаров на них по поставщикам и кодам категорий и отобразить результаты. Или, другими словами, для каждой уникальной комбинации Vendor/Category сложите все суммы ваучеров, которые имеют эту комбинацию Vendor/Category.
Использование синтаксиса SUM (Fieldname) OVER (PARTITION BY fieldname, fieldname).
Таким образом, конечный результат должен выглядеть примерно так...
Code Vendor Amount 123-45 Acme $5000.00 123-45 Apple $4200.00 123-46 Acme $750.00
С учетом вышесказанного, вот SQL, который Query Manager отображает для получения набора результатов, который я показал выше:
SELECT DISTINCT D.CATEGORY_CD, D.TN_DESCR1000, C.VENDOR_ID, E.NAME1, SUM ( A.MERCH_AMT_VCHR) OVER (PARTITION BY D.CATEGORY_CD, C.VENDOR_ID),E.SETID,E.VENDOR_ID
FROM PS_PO_LINE_MATCHED A, PS_PO_LINE B, PS_PO_HDR C, PS_ITM_CAT_TBL D, PS_VENDOR E, PS_PYMNT_VCHR_XREF F
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.PO_ID = B.PO_ID
AND A.LINE_NBR = B.LINE_NBR
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.PO_ID = C.PO_ID
AND D.CATEGORY_ID = B.CATEGORY_ID
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ITM_CAT_TBL D_ED
WHERE D.SETID = D_ED.SETID
AND D.CATEGORY_TYPE = D_ED.CATEGORY_TYPE
AND D.CATEGORY_CD = D_ED.CATEGORY_CD
AND D.CATEGORY_ID = D_ED.CATEGORY_ID
AND D_ED.EFFDT <= SYSDATE)
AND ( F.SCHEDULED_PAY_DT >= TO_DATE('2010-07-01','YYYY-MM-DD')
AND F.SCHEDULED_PAY_DT <= TO_DATE('2011-06-30','YYYY-MM-DD'))
AND D.CATEGORY_CD LIKE :1
AND E.VENDOR_ID = C.VENDOR_ID
AND A.BUSINESS_UNIT = F.BUSINESS_UNIT
AND A.VOUCHER_ID = F.VOUCHER_ID
ORDER BY 1
Основная проблема: это работает нормально, но может запрашивать только один код категории за раз. Коды категорий состоят из 5 цифр, трехзначного "Класса", за которым следует тире, а затем двухзначного подкласса. У меня есть список из 375 кодов категорий, для которых мне нужно получить результат этого запроса.
Я настроил запрос для этой версии, который позволяет вводить подстановочный знак (так 123-%%), но это все еще около ста отдельных запусков запроса. Диспетчер запросов позволяет использовать тип выражения "В списке" в Критериях, но для этого необходимо вручную вводить каждую запись в списке.
Я пытаюсь установить его так, чтобы я мог вставить незашифрованную копию списка кодов в выражение с надлежащими кавычками / запятыми и заставить его оценить это, чтобы получить объединенный список всех указанных кодов NIGP. Поле Prompt, созданное Query Manager, не позволяет вставлять списки (насколько я знаю).
Попытка решения: я просмотрел страницу по адресу http://peoplesoft.ittoolbox.com/groups/technical-functional/peoplesoft-other-l/create-an-expression-in-psoft-90-query-to-paste-a-list-of-emplids-2808427 и я попробовал некоторые ответы, приведенные там, но ни один из них не сработал. На этой странице я попробовал этот модифицированный SQL (очевидно, список кодов немного урезан для отображения здесь):
SELECT DISTINCT D.CATEGORY_CD, D.TN_DESCR1000, C.VENDOR_ID, E.NAME1, SUM ( A.MERCH_AMT_VCHR) OVER (PARTITION BY D.CATEGORY_CD, C.VENDOR_ID),E.SETID,E.VENDOR_ID
FROM PS_PO_LINE_MATCHED A, PS_PO_LINE B, PS_PO_HDR C, PS_ITM_CAT_TBL D, PS_VENDOR E, PS_PYMNT_VCHR_XREF F
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.PO_ID = B.PO_ID
AND A.LINE_NBR = B.LINE_NBR
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.PO_ID = C.PO_ID
AND D.CATEGORY_ID = B.CATEGORY_ID
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_ITM_CAT_TBL D_ED
WHERE D.SETID = D_ED.SETID
AND D.CATEGORY_TYPE = D_ED.CATEGORY_TYPE
AND D.CATEGORY_CD = D_ED.CATEGORY_CD
AND D.CATEGORY_ID = D_ED.CATEGORY_ID
AND D_ED.EFFDT <= SYSDATE)
AND ( F.SCHEDULED_PAY_DT >= TO_DATE('2010-07-01','YYYY-MM-DD')
AND F.SCHEDULED_PAY_DT <= TO_DATE('2011-06-30','YYYY-MM-DD'))
AND D.CATEGORY_CD = '005-00' OR D.CATEGORY_CD IN ('015-00,'' '015-06,'' '015-10,'' '615-07'')
AND E.VENDOR_ID = C.VENDOR_ID
AND A.BUSINESS_UNIT = F.BUSINESS_UNIT
AND A.VOUCHER_ID = F.VOUCHER_ID
ORDER BY 1
И приведенный выше SQL дает мне ошибку ORA-00907. Кто-нибудь сталкивался с этой проблемой раньше? Массивная стена текста, я знаю. Мои извенения. Это мой первый пост здесь, и я стараюсь не пропустить ничего актуального.
У меня возникла непосредственная проблема, которая подтолкнула этот вопрос, но этот запрос - лишь верхушка очень большого айсберга, и в какой-то момент мне нужно найти способ вставить списки открытого текста в качестве критериев с помощью диспетчера запросов предпочтительно таким способом, который хорошо сочетается с аналитической группировкой.
TL; DR версия:
Использование Peoplesoft Query Manager для создания аналитической суммы с группированием с использованием OVER, PARTITION BY. Когда я пытаюсь вставить список в критерии, выдается ошибка ORA-00907.
Любая помощь будет принята с благодарностью. Спасибо!
4 ответа
По ссылке, которую вы разместили, я вижу 2 способа сделать то, что вы пытаетесь достичь. Я также заметил, что вы попробовали третий метод.
Способ 1
- Критерии> Добавить критерии
- Тип выражения: символ
- Длина: 255
- Текст выражения: D.CATEGORY_CD IN ('015-00','015-06','015-10','615-07') И 1
- Тип условия: равно
- Постоянная: 1
Способ 2
- Критерии> Добавить критерии
- Поле: D.CATEGORY_CD
- Тип условия: в списке
- Значение: 015-00','015-06','015-10','615-07
Метод 3 (Ваш метод)
- Критерии> Добавить критерии
- Поле: D.CATEGORY_CD
- Тип условия: равно
- Определите выражение: "015-00" ИЛИ D.CATEGORY_CD IN ("015-00", "015-06", "015-10", "615-07")
Вопрос) Соответствует ли приведенное ниже тексту, который вы помещаете в поле "Выражение"?
'015-00' ИЛИ D.CATEGORY_CD IN ('015-00', '015-06', '015-10', '615-07')
Если нет, что вы кладете в эту коробку?
Хорошо, после некоторой дополнительной настройки я обнаружил, что, по моему мнению, является основной проблемой.
Ошибка в этом случае двоякая. Частично это была моя ошибка (я не проверял, чтобы Peoplesoft искажал кавычки, которые я извлек из Word), и отчасти это было из-за того, как Query Manager интерпретирует некоторые виды функций (некоторые вещи нужно заключить в оператор Case When). чтобы его правильно оценили).
Во-первых, часть "Моя ошибка":
Каждый раз, когда я вставлял в свой список тестовых кодов NIGP, я делал это из файла, который хранил в Microsoft Word.
Имеет, вероятно, удобную функцию "заменять прямые кавычки умными кавычками". Peoplesoft сходит с ума, когда представляет "умную цитату" и отображает их в виде перевернутых вопросительных знаков (возможно, есть технический термин, я его не знаю).
Поэтому, когда я проверял предложения (например, исправлял порядок кавычек / запятых, предложенный @Rene Nyffenegger и @WayneH), я начинал с базового тестового запроса, добавлял выражения и тестировал его, сохраняя его как отдельный запрос., Если бы они не работали, я бы вернулся к базовому запросу. Таким образом, я мог бы повторять изменения и сохранять потенциальные тесты как разные версии.
Моя ошибка заключалась в том, что я не сохранял разные версии, не оставлял приложение и не возвращался обратно. Когда вы сохраняете запрос, покидаете страницу, идете куда-то еще в Peoplesoft, а затем возвращаетесь назад, чтобы открыть Query Manager, он фактически показывает, что выполняет преобразование символов. Вы не сможете увидеть это, если не сделаете этого. Хотя Query Manager делает это. Поэтому он бросал персонажа, которого менеджер запросов не мог бы распознать, но не показывал мне символ, который он не мог бы распознать.
Недавно у меня появился новый рабочий ПК, и теперь я отключил автозамену Smart Quotes для будущего использования.
Во-вторых, "Менеджер запросов": часть:
На этой версии, которую я получил, я использовал обертывание функции "IN" внутри оператора Case. Я обнаружил, что многие функции SQL, когда используются "простые" (как я определил бы их путем простого вставки копий со страниц определений Oracle и заполнения соответствующих переменных), обычно вызывают изжогу из PS/Query (Query Manager)., Но если вы поместите их в оператор CASE...WHEN...END, который оценивает результат функции, а затем создадите критерий, который выбирает на основе определенных значений этого результата, функция будет работать и правильно отображать результат.
Так, для примера, настройте это выражение (как в примере из @qyb2zm302). Я использую коды, отличные от того, что был в моем исходном примере, но они работают одинаково (все они пятизначные, символьные коды, состоящие из трех цифр, тире, а затем двух цифр)
Case when E.CATEGORY_CD IN
('375-15', '375-30', '375-54', '375-60', '380-30','938-63')
then 'true'
else 'false'
end
А затем установить критерии:
AND
Case when E.CATEGORY_CD IN
('375-15', '375-30', '375-54', '375-60', '380-30','938-63')
then 'true'
else 'false'
end
= 'true'
Он будет работать до конца и вернет все строки, которые имеют этот код категории.
Если вы не хотите этого делать, вы можете сделать это, как в методе @ qyb2zm302. Единственным недостатком этого в Query Manager является то, что вам нужно вводить их в отдельные строки в "Списке", и если вы можете только копировать -паста 25 за раз.
Оборачивая его в Case Statement, вы можете вставить его непосредственно в Expression, что намного лучше для больших списков.
Решения:
Выше приведен код, с которым я работал. Ради упрощения это немного упрощает, но это работает.
В Списке работает через встроенную опцию Query Manager, если вы вручную заполняете список
D.CATEGORY_CD = '005-00' ИЛИ работает до тех пор, пока вы включите его в регистр дел
D.CATEGORY_CD IN ('015-00','015-06','015-10','615-07') работает до тех пор, пока вы включите его в регистр дел
Peoplesoft ненавидит Smart Quotes. Ничто из вышеперечисленного не будет работать, если вы копируете кавычки непосредственно из Word, но вы не увидите его, если не сохраните, не выйдете и не вернетесь к тому же запросу в режиме редактирования.
Форматирование важно. Все вышеперечисленное требует правильного форматирования запятой / цитаты, как указано Рене и Уэйном. Значение: ("xxx-xx", "xxx-01", "xxx-02") и т. Д.
Спасибо всем, кто помог в этом! Я не думаю, что раньше занимался таким сложным делом по любому вопросу, но я думаю, что это часть процесса обучения. Поскольку все опубликованные ответы являются действительными и правильными (или, по крайней мере, частью более крупного "правильного"), я собираюсь отметить их все.
D.CATEGORY_CD IN ('015-00,'' '015-06,'' '015-10,'' '615-07'')
часть выглядит подозрительно для меня
Так как '' внутри строки "оценивает" до одного ' первая строка
'015-00,'' '
с последующим (не строка)
015-06,
Следующие '' это, вероятно, вещь, на которую натыкается синтаксический анализатор, так как это довольно бессмысленно.
Редактировать попробуйте с D.CATEGORY_CD IN ('015-00', '015-06', '015-10', '615-07'),
Я думаю, что критерии D.CATEGORY_CD ставят вам проблемы, я изменил двойные кавычки на одинарные, а потом мне все еще показалось странным. Затем я замечаю запятые внутри ваших кавычек, а не между ними, попробуйте сделать так, чтобы строка с одним критерием выглядела так:
до:
OR D.CATEGORY_CD IN ('015-00,'' '015-06,'' '015-10,'' '615-07'')
после:
OR D.CATEGORY_CD IN ('015-00', '015-06', '015-10', '615-07')
Кроме того, "IN" является подразумеваемым "ИЛИ", и я не уверен, что если у вас есть круглые скобки вокруг двух D.CATEGORY_CD, я бы просто поместил один дополнительный код в критерии IN и удалил строку "D.CATEGORY_CD =":
до:
AND D.CATEGORY_CD = '005-00' OR D.CATEGORY_CD IN ('015-00', '015-06', '015-10', '615-07')
после:
AND D.CATEGORY_CD IN ('015-00', '015-06', '015-10', '615-07', '005-00')
Конечно, вы уже заказываете по CATEGORY_CD, вы можете удалить этот критерий и вытащить все категории за один прогон (то есть, если нет слишком много строк для Excel), а затем вы можете также включить VENDOR_ID или NAME1 в ORDER. Пункт BY.
Надеюсь, это поможет вам.