得到错误，如"ORA-32044:在执行带查询的递归时检测到周期". [英] Getting error as "ORA-32044: cycle detected while executing recursive WITH query"

问题描述

我收到以下错误消息:"ORA-32044:执行递归WITH查询时检测到周期" 在Oracle中执行以下查询时.

I am getting error as "ORA-32044: cycle detected while executing recursive WITH query" while executing the following query in Oracle.

WITH EmpsCTE (affiliation_id, from_customer_id,to_customer_id, to_name, level1)
AS
(
SELECT affiliation_id, from_customer_id,to_customer_id, to_name, 0
 FROM affiliation aff
 WHERE to_customer_id != from_customer_id
 and to_customer_id = 1000022560394
UNION ALL
SELECT aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1
 FROM affiliation aff
 INNER JOIN EmpsCTE  m
 ON aff.to_customer_id = m.from_customer_id
)
SELECT * FROM EmpsCTE;

推荐答案

您的代码可以正常工作，但仅当一个数据条件满足时，即您的to_customer(1000022560394)自己首先在某个级别进行交易后，交易只被退还给他.

Your code will working fine except only for one data condition that is when your to_customer (1000022560394) himself have started the transaction in the first place and after some level of transaction its being returned to him only.

例如 示例数据集

在这种情况下，即使在事务结束时，查询的递归"部分也将发现其所有条件均为真，因为数据将同时存在于正常表和增量数据集中.

For this case, your Recursive part of query will find all its conditions true even at the end of the transaction, for the data will be there both in your normal table and incremental dataset.

一种解决方案是创建一个匹配标志，以确定其遇到的次数并避免无限循环:

WITH EmpsCTE (affiliation_id, from_customer_id,to_customer_id, to_name,level1,match_count)  
AS  
(  
SELECT affiliation_id, from_customer_id,to_customer_id, to_name, 0, 0 match_count  
 FROM affiliation aff  
 WHERE to_customer_id != from_customer_id  
 and to_customer_id = 1000022560394  
UNION ALL  
SELECT aff.affiliation_id, aff.from_customer_id,aff.to_customer_id, aff.to_name, m.level1 + 1,1 match_count  
 FROM affiliation aff  
 INNER JOIN EmpsCTE  m  
 ON aff.to_customer_id = m.from_customer_id  
 where m.match_count=0  
)  
SELECT * FROM EmpsCTE;  

这篇关于得到错误，如"ORA-32044:在执行带查询的递归时检测到周期".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！