Home » SQL & PL/SQL » SQL & PL/SQL » find circular reference in data
find circular reference in data [message #671343] Thu, 23 August 2018 05:07 Go to next message
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 #671344 is a reply to message #671343] Thu, 23 August 2018 05:16 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Desired output should be

g1>g2 
g1>g3
g1>g2>g4
g1>g2>g4>g1
Re: find circular reference in data [message #671345 is a reply to message #671344] Thu, 23 August 2018 05:50 Go to previous messageGo to next message
quirks
Messages: 81
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 #671346 is a reply to message #671345] Thu, 23 August 2018 06:40 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Thanks @quirks , I am still trying to understand query but in general can we say this can be used to generate hierarchical view
Re: find circular reference in data [message #671347 is a reply to message #671346] Thu, 23 August 2018 06:55 Go to previous message
Solomon Yakobson
Messages: 2985
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.
Previous Topic: consistency constraint ON store Procedure
Next Topic: SQLException encounter while executing data trigger.
Goto Forum:
  


Current Time: Mon Sep 21 12:17:14 CDT 2020