ORACLE CONNECT BY LEVEL 产生重复行

3
我正在尝试从一个包含多个笔记的字符串中提取数据,这些笔记被附加到一个varchar(4000)列上。我在查询中使用了正则表达式和函数的混合,并且使用了CONNECT BY LEVEL和regexp_count,因为我不知道是否只有一个笔记或多个笔记。当我返回结果时,我注意到有很多重复的行。我认为这纯粹是由于CONNECT BY引起的,这是我之前没有使用过的东西,所以我认为我可能漏掉了什么。
以下是查询语句:
    select 
  id,
  substr(regexp_substr(VALUE,'^LOCKED BY USER: +(.*)',1,level,'m'),17) as LOCKUSER,
  substr(regexp_substr(VALUE,'^LOCKED ENTITY: +(.*)',1,level,'m'),16) as LOCKED_ENTITY,
  TO_DATE(LTRIM(regexp_substr(VALUE,'^LOCKED AT: ([[:digit:]]{2}/[[:digit:]]{2}/[[:digit:]]{4}\.?)',1,level,'m'),'LOCKED AT: '),'DD/MM/YYYY') as Dates_Locked,
  substr(regexp_substr(VALUE,'^LOCK NOTES: +(.*)',1,level,'m'),13) as LOCK_NOTES,
  'LOCK' as ACTION
 from TABLE
 where regexp_substr(VALUE,'^LOCK NOTES: +(.*)',1,level,'m') IS NOT NULL
  AND TO_DATE(LTRIM(regexp_substr(VALUE,'LOCKED AT: ([[:digit:]]{2}/[[:digit:]]{2}/[[:digit:]]{4}\.?)',1,level,'m'),'LOCKED AT: '),'DD/MM/YYYY') >= (SYSDATE -365)
connect by level <= regexp_count(VALUE,CHR(10)||CHR(13));

如果我对一张有10K条记录的表运行此操作,我永远不会得到任何结果,我认为这是由于它返回的大量重复行。有没有办法让我防止这种情况的发生?

非常感谢。


确认一下,我尝试过使用SELECT DISTINCT,但没有成功。 - user7329642
1个回答

7

目前,您的CONNECT BY只限制了层次级别,并没有提供任何条件来匹配子行和父行。这意味着在一个有多个行的表中,每一行都是其他每一行的子行。这将产生大量的结果集。

如果我理解正确,您正在尝试使用层次功能从每个单独的行中拉取多个值。因此,您希望每一行既是父行又是子行。我建议尝试:

CONNECT BY id = PRIOR id
AND prior sys_guid() is not null
AND level <= regexp_count(VALUE,CHR(10)||CHR(13))

感谢 @kfinity 指出需要使用 sys_guid() 函数来防止 CONNECT BY LOOP。


谢谢Dave,我刚试了一下,但不幸的是它报错了...ORA-01436: 用户数据中的CONNECT BY循环 01436. 00000 - "用户数据中的CONNECT BY循环" *原因: *解决方案: - user7329642
3
如果您遇到ORA-01436错误,请尝试添加 and prior sys_guid() is not null - kfinity
太棒了!你在我发布问题的同时就已经回答了它!我刚刚添加了 "and prior sys_guid() is not null",现在它运行得非常好!感谢你的帮助! - user7329642

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接