#sql #oracle #oracle11g
#sql #Oracle #оракул11g
Вопрос:
У меня следующий запрос:
SELECT wm_concat(DISTINCT NAME) as Methods FROM TPM_TRAININGPLAN
JOIN TPM_DELIVERYMETHODS USING (METHODID)
WHERE PROJECTID=735 AND VERSIONID=1
ORDER BY NAME
В нашей производственной базе данных (некоторые очень дорогие артикулы Oracle, которые стоят дороже, чем мой дом *), он возвращает:
I/OLT,ILT-WEB,OLT,QRG
Однако в моем локальном экземпляре Oracle Express (11g), который я использую для разработки, я получаю сообщение об ошибке:
>[Error] Script lines: 13-16 ------------------------
ORA-00904: "WM_CONCAT": invalid identifier
Script line 13, statement line 1, column 7
Oracle Express не поддерживает это, или есть обходной путь (например, могу ли я скопировать функцию с нашего производственного сервера или запустить какой-нибудь скрипт, чтобы «имитировать» ее?) Спасибо!
* Нет, серьезно, это на самом деле стоит дороже, чем мой дом.
Обновить:
Как и было предложено, я попробовал функцию LISTAGG (которая действительно включена как в Express 11g, так и в артикул «Должны ли мы купить лицензию Oracle или вторую яхту». Кажется, это почти работает, но имеет несколько недостатков, для которых я не могу найти хорошего обходного пути. Во-первых, запрос:
SELECT LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) Methods FROM
TPM_TRAININGPLAN
JOIN TPM_DELIVERYMETHODS USING (METHODID) WHERE PROJECTID=240 AND VERSIONID=1
Вернет:
COMM,COMM,ILT-F2F,ILT-F2F,ILT-F2F,ILT-F2F,ILT-F2F
Тем не менее, я хочу удалить дубликаты. Изменение NAME на DISTINCT NAME вызовет ошибку DISTINCT option not allowed for this function
Ну, отлично, мы просто добавим туда CTE:
WITH Methods AS
(SELECT DISTINCT NAME
FROM TPM_TRAININGPLAN
JOIN TPM_DELIVERYMETHODS USING (METHODID) WHERE PROJECTID=240 AND VERSIONID=1)
SELECT LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) Methods FROM Methods
Это работает, однако я забыл упомянуть, что я запускаю все это как вложенный select в большем запросе. Кажется, у вас могут быть вложенные выборки, которые включают CTE, однако в запросе вы больше не можете ссылаться на корневой запрос, только на CTE. Итак, теперь я не могу заменить 240 и 1 соответствующими значениями из строки, в которой я нахожусь.
Комментарии:
1. en.wikipedia.org/wiki/Stock-keeping_unit
2. @MikeChristensen Я не следую этой строке: «Итак, теперь я не могу заменить 240 и 1 соответствующими значениями из строки, в которой я нахожусь». Вам нужно сгруппировать агрегацию по идентификатору проекта и / или идентификатору версии?
3. Нет, я выбираю кучу разных строк проекта / версии, и для каждой из них я хочу создать объединенный список, используя вложенный выбор. На самом деле я решил эту проблему, написав свою собственную версию wm_concat, которая работает на Oracle Express.
Ответ №1:
если вы находитесь в 11GR2, вы должны быть в состоянии выполнить listagg
with fake_data as(
select 'I/OLT' NAME , 1 aa from dual
union all
select 'ILT-WEB' NAME,2 from dual
union all
select 'OLT' NAME,3 from dual
union all
select 'QRG' NAME,4 from dual
)
SELECT
LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY name) name_list
FROM fake_data
;
NAME_LIST
--------------------------
I/OLT, ILT-WEB, OLT, QRG
У меня нет Oracle Express 11gR2 для тестирования этого (только enterprise).
Но Oracle Express не поддерживает Java, но вы можете попытаться реализовать его с помощью пользовательских типов (опять же, у меня нет Express для тестирования!)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402
Комментарии:
1. Выглядит почти идеально! Я попробовал и добавил свои комментарии выше. Но я думаю, что мы на правильном пути.