由于WMSYS_CONCAT()只适用于oracle10.0及之前的版本,且该oracle函数并不是官方规定的函数,导致在使用的过程中会发生因为oracle数据库版本升级后,该函数失效,从而使得sql语句报错:[Err] ORA-00904: "WM_CONCAT": invalid identifier
如下述sql语句:
select
WM_CONCAT(STAGE) AS stage
from
TB_TESTDATA
where
serial_no in ('AAA','BBB','CCC')
group by serial_no
sql报错信息如下:
[SQL]select
WM_CONCAT(STAGE) AS stage
from
TB_TESTDATA
where
serial_no in ('AAA','BBB','CCC')
group by serial_no
[Err] ORA-00904: "WM_CONCAT": invalid identifier
如blog:http://www.askmaclean.com/archives/wmsys-wm_concat.html所述
我们修改为如下sql语句:
SELECT
SERIAL_NO,
listagg(STAGE,',') within GROUP(order BY STAGE) STAGE,
listagg(EQP_NO,',') within GROUP(order BY EQP_NO) EQP_NO,
listagg(FIXTURE_NO,',') within GROUP(order BY FIXTURE_NO) FIXTURE_NO
from
TB_TESTDATA
where
serial_no in ('AAA,'BBB','CCC') GROUP BY SERIAL_NO
则能正确的执行了。
记录仅供以后参考,好记性不如烂笔头