ORACLE/SQL: wm_concat & упорядочить по
Я использую оракул 11 (не уверен насчет точной версии, но поскольку LISTAGG не работает, я полагаю, это не выпуск 2) через ODBC и Crystal Reports 2008.
Вот проблема у меня:
Вот таблица:
TABLE ODB.TASK_CARD_CONTROL
------------------------------------------
task_card control_category code
------------------------------------------
1 zone 17
1 zone 33
1 zone 21
2 zone 18
2 zone 05
3 zone 55
3 zone 32
3 zone 72
Я использую функцию WM_CONCAT, чтобы получить что-то вроде этого:
task_card zones
1 17,33,21
2 18,05
3 55,32,72
Вот SQL для этого:
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD
но я бы хотел, чтобы зоны были отсортированы, поэтому я попробовал это:
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL
ORDER BY CODE)
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD
но по какой-то причине он возвращает следующую ошибку:
Failed to retrieve data from the database.
Details: 42S22:[Oracle][ODBC][Ora]ORA-00904:
"ODB"."TASK_CARD_CONTROL"."CONTROL_CATEGORY" : invalid identifier
Я действительно не понимаю, что я делаю здесь неправильно... Кто-нибудь может дать мне подсказку?
6 ответов
Вы не можете ссылаться на ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY снаружи внутреннего запроса. Пытаться:
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
ORDER BY CODE)
GROUP BY TASK_CARD
Для тех, кто все еще использует wm_CONCAT (он же более старые версии БД): решение состоит в том, чтобы добавить отдельное условие, затем он также применяет возрастающий порядок к объединенным значениям.
Не спрашивайте, почему это не задокументировано, но это сработает.
Кроме того, при использовании порядка by в подзапросе, предшествующем wm_concat, порядок будет просто рандомизирован, поэтому его рекомендовать не следовало.
Пример для запрашиваемого SQL:
SELECT TASK_CARD, WM_CONCAT(distinct code) as ZONES
FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD;
Просто имейте в виду, что отдельный параметр не работает при использовании в процедурах / пакетах.
Если вы дадите подзапросу в предложении from имя, то сможете ссылаться на столбцы в самом подзапросе.
SELECT t1.TASK_CARD
, WM_CONCAT(t1.code) as ZONES
FROM
(SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL ORDER BY CODE) t1
WHERE t1.CONTROL_CATEGORY = 'ZONE'
GROUP BY t1.TASK_CARD
- Заказать по нужному столбцу, затем
- Порядок в порядке внешних запросов по номеру строки.
- Используйте функцию.
Эта функция имеет логику для последнего порядка row num:
Select wmsys.wm_concat(t) CONCAT from
(
Select t from (
Select t from (
Select 'aa' t from dual
union
Select 'zz' t from dual
union
Select 'pp' t from dual
union
Select 'll' t from dual
union
Select 'mm' t from dual
union
Select 'xx' t from dual
union
Select 'cc' t from dual
) a
order by t
) order by rownum
) t
LISTAGG был представлен в 11g Release 2.
Поэтому в версии Oracle до 11g где LISTAGG не поддерживается, вы можете использовать функции ROW_NUMBER() и SYS_CONNECT_BY_PATH.
См. Методы агрегации строк в Oracle
SELECT task_card,
LTRIM(MAX(SYS_CONNECT_BY_PATH(code,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS zones
FROM (SELECT task_card,
code,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) AS curr,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) -1 AS prev
FROM table_name)
GROUP BY task_card
CONNECT BY prev = PRIOR curr AND task_card= PRIOR task_card
START WITH curr = 1;
НОТА
Никогда не используйте WM_CONCAT так как это недокументированная функция, и она была удалена из версии 12c.
Любое приложение, на которое полагались wm_concat функция не будет работать после обновления до 12c, С тех пор он был удален. См. Почему бы не использовать функцию WM_CONCAT в Oracle?
SQL> select banner from v$version where rownum = 1;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL> SELECT object_name
2 FROM dba_objects
3 WHERE owner='WMSYS'
4 AND object_name LIKE 'WM\_%' ESCAPE '\';
OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES
20 rows selected.
SQL>
Вы получите ошибку "неверный идентификатор":
SQL> SELECT banner FROM v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
*
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier
Поэтому нет смысла полагаться на недокументированную функцию, которая больше не доступна в последних версиях.
Используйте ListAgg вместо wm_concat
SELECT TASK_CARD, ListAgg(code) within (order by code asc) as ZONES
http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html