(使用Oracle 11.2)
我有一个相当复杂的sql
wm_concat( distinct abc )
预计会返回一些varchar2(4000)兼容的结果.
它导致ORA-00932：在某些coalesce中使用的select中的数据类型不一致(some_varchar_col,wm_concat(…)).
所以我尝试通过两种不同的方法来构建它：
dbms_lob.substr( ...,4000 ) -- L) tried even with 3000 in case of "unicode byte blow-up" cast( ... as varchar2(4000)) -- C) tried even with 3000 in case of "unicode byte blow-up"
(在视图中使用,但玩它表示,它与视图无关)
根据列和其他运算符,我得到N)没有结果或O)ORA-22922：
select * from view_with_above_included where rownum <= 100
> N)我的Eclipse Data Explorer JDBC连接返回时没有任何结果(没有没有结果的列,没有(0行受影响),只有查询时间统计信息). (这可能是一个内部例外,不是这样处理的？)
> O)
ORA-22922: nonexistent LOB value ORA-06512: in "SYS.DBMS_LOB",line 1092 ORA-06512: in line 1
奇怪的是,以下测试查询工作：
-- rownum <= 100 would already cause the above problems select * from view_with_above_included where rownum <= 10
要么
select * from view_with_above_included
但是查看实际的聚合数据并不会显示长度超过1000个字符的聚合数据.
幸运的是,它适用于自11.2以来提供的listagg(…)函数(我们已经在运行),因此我们不必进一步调查：
listagg( abc,',' ) within group ( order by abc )
(正如人们应该知道的那样,wm_concat(…)是一些内部和官方不支持的功能.)
实现不同功能的rather nice solution(因为它不是那么臃肿)是通过自引用正则表达式功能实现的,这在许多情况下应该起作用：
regexp_replace( listagg( abc,' ) within group ( order by abc ),'(^|,)(.+)(,\2)+','\1\2' )
(也许/希望我们将来会看到一些有效的listagg(不同的abc)功能,它会像wm_concat语法一样非常整洁和酷.例如,这是没有问题,因为很长一段时间与Postgres的string_agg(distinct abc)1)
-- 1: postgres sql example: select string_agg( distinct x,' ) from unnest('{a,b,a}'::text[]) as x`
如果列表超过4000个字符,则不能再使用listagg(再次使用ORA-22922).
但幸运的是我们可以在这里使用xmlagg函数(如here所述).
如果你想在这里实现4000-chars截断结果的不同,你可以取消注释(1)标记的行.
-- in smallercase everything that could/should be special for your query
-- comment in (1) to realize a distinct on a 4000 chars truncated result
WITH cfg AS (
SELECT
',' AS list_delim,'([^,]+)(,\1)*(,|$)' AS list_dist_match,-- regexp match for distinct functionality
'\1\3' AS LIST_DIST_REPL -- regexp replace for distinct functionality
FROM DUAL
)
SELECT
--REGEXP_REPLACE( DBMS_LOB.SUBSTR( -- (1)
RTRIM( XMLAGG( XMLELEMENT( E,mycol,listdelim ).EXTRACT('//text()')
ORDER BY mycol ).GetClobVal(),LIST_DELIM )
--,4000 ),LIST_DIST_MATCH,LIST_DIST_REPL ) -- (1)
AS mylist
FROM mytab,CFG
