递归查询中使用CTE表作为' START WITH '子句的一部分

Question

我似乎不能使用CTE表作为下面递归查询的一部分?

我有两个CTE表。一个获取一堆石斑鱼代码，另一个将这些石斑鱼代码分解成"子代码"。(exploded_codes)，我想在下面的查询中使用。当我将EXPLODED_CODES CTE表添加到主查询的IN()子句中时(如下所示)，没有返回任何数据(尽管没有错误)。但是，如果我将EXPLODED_CODES CTE表上的简单选择语句返回的值硬编码到IN()子句中，查询将按预期返回!知道这是为什么吗?

这是我的递归查询。有没有什么理由可以让任何人认为使用CTE表不起作用，但是硬编码CTE表包含的值就可以?我知道它与递归逻辑有关，因为当我将starts with更改为where并注释掉connect by子句时，它返回一些数据(尽管它不是递归查询使用硬编码值返回的完整父子层次结构)。

select *
from events 
start with 
events.event_cd in (
select event_cd from exploded_codes --This code returns no data
--22600750,135148330,107919568 --These are the values in the CTE and returns correct data
)
connect by nocycle ((prior parent_event_id = event_id and prior event_id <> event_id)  
or (prior event_id = parent_event_id) ) 

Answer 1

这基本上就是我要做的。然而，它在我的模拟中工作得很好，像这样:

with mytable as (
select 1 as pk1, 123 as event_id, 123 as parent_event_id, 777 as event_cd from dual
union select 2, 456, 123, 777 as event_cd from dual
union select 3, 756, 423, 999 as event_cd from dual
)
--select * from mytable
, codes as (
select 777 as event_cd from dual union select 88 as event_cd from dual union 
select 111 as event_cd from dual
)
select myTable.*--, level, SYS_CONNECT_BY_PATH(event_id, '/') as path
from 
myTable  
start with event_cd in (select event_cd from codes)  
connect by nocycle (prior parent_event_id = event_id and prior event_id <> event_id)
or (prior event_id = parent_event_id)

然而，当我查询实际的数据库表时，它没有返回任何行，这取决于我如何填充CTE表(事件)，尽管我的表最终以每次相同的值结束。

我似乎无法理解的是，当我从我的CODES表中取消注释下面一行时，是当我的递归查询不返回任何东西时。然而，当我像下面这样硬编码这些值时，我的递归查询返回良好。我试过将数据类型转换为NUMBER(这就是event_cd存储在EVENT表中的方式)，但没有影响。

with codes as (
--This method of populating the table causes my recursive query to not return anything 
--select xcode from xr_template_codes where xtype = 'event-set' and SECTION_DISPLAY = 'Coding Summary'
--So does this method (which is the equivalent of the above).
--select xcode from xr_template_codes where  xcode = 107919402
--union
--select xcode from xr_template_codes where xcode = 101320306
--This method seems to work fine. These are just the two values returned from the XR_TEMPLATES_CODES table.
select 101320306 as event_cd from dual union select 107919402 as event_cd from dual

)
select *
from 
event       
start with 
event.event_cd in (select to_number(event_cd) from codes)
and event.id = 15169295                              

connect by nocycle (prior parent_event_id = event_id and prior event_id <> event_id)
or (prior event_id = parent_event_id)