find circular reference in data [message #671343] |
Thu, 23 August 2018 05:07 |
|
purnima1
Messages: 79 Registered: June 2014
|
Member |
|
|
Hi Experts ,
I have one table which has some circular reference in that.
First row g1 > g2
2nd row g1 >g3
g2>g4
g4>g1
So we can make out that there is relation g1 > g2>g4 . So I need this output in new column. So please help in resolving this issue
create table test (gold_id varchar2(10), rel_gold_id varchar2(10))
begin
insert into test values ('g1','g2');
insert into test values ('g1','g3');
insert into test values ('g2','g4');
insert into test values ('g4','g1');
commit;
end ;
|
|
|
|
Re: find circular reference in data [message #671345 is a reply to message #671344] |
Thu, 23 August 2018 05:50 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
WITH
TEST_DATA (GOLD_ID, REL_GOLD_ID)
AS
(SELECT 'g1', 'g2' FROM DUAL
UNION
SELECT 'g1', 'g3' FROM DUAL
UNION
SELECT 'g2', 'g4' FROM DUAL
UNION
SELECT 'g4', 'g1' FROM DUAL),
RESULT (OUTPUT, GOLD_ID, REL_GOLD_ID)
AS
(SELECT TEST_DATA.GOLD_ID || '>' || TEST_DATA.REL_GOLD_ID AS OUTPUT, TEST_DATA.GOLD_ID, TEST_DATA.REL_GOLD_ID
FROM TEST_DATA
WHERE GOLD_ID = 'g1'
UNION ALL
SELECT RESULT.OUTPUT || '>' || TEST_DATA.REL_GOLD_ID AS OUTPUT, TEST_DATA.GOLD_ID, TEST_DATA.REL_GOLD_ID
FROM RESULT
JOIN TEST_DATA ON (RESULT.REL_GOLD_ID = TEST_DATA.GOLD_ID))
CYCLE GOLD_ID SET CYCLE TO '1' DEFAULT '0'
SELECT TRIM(OUTPUT)
FROM RESULT
WHERE CYCLE = 0
|
|
|
|
Re: find circular reference in data [message #671347 is a reply to message #671346] |
Thu, 23 August 2018 06:55 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
select 'g1' || sys_connect_by_path(rel_gold_id,'>') path
from test
where prior rel_gold_id != 'g1'
or level = 1
start with gold_id = 'g1'
connect by nocycle gold_id = prior rel_gold_id
/
PATH
---------------
g1>g2
g1>g2>g4
g1>g2>g4>g1
g1>g3
SQL>
SY.
|
|
|