I have a database that takes a feed from a legacy system that has a rather illogical data structure that I need to tie together. The records chain together where the value in original_col equals the value of another records new_col. Using a connect by prior query I have the following results:-
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
with data_sample as (
SELECT 'A129994' AS ORIGINAL_COL ,'A129993' AS NEW_COL FROM DUAL
UNION
SELECT 'A129996' AS ORIGINAL_COL ,'A129994' AS NEW_COL FROM DUAL
UNION
SELECT 'A129999' AS ORIGINAL_COL ,'A129996' AS NEW_COL FROM DUAL
UNION
SELECT 'B129994' AS ORIGINAL_COL ,'B129993' AS NEW_COL FROM DUAL
UNION
SELECT 'B129996' AS ORIGINAL_COL ,'B129994' AS NEW_COL FROM DUAL
UNION
SELECT 'B129999' AS ORIGINAL_COL ,'B129996' AS NEW_COL FROM DUAL
UNION
SELECT 'C129994' AS ORIGINAL_COL ,'C129993' AS NEW_COL FROM DUAL
UNION
SELECT 'C129996' AS ORIGINAL_COL ,'C129994' AS NEW_COL FROM DUAL
)
select original_col, new_col, level as connect_by_level
CONNECT BY PRIOR original_col = new_col;
Results:-
ORIGINAL_COL NEW_COL CONNECT_BY_LEVEL
A129994 A129993 1
A129996 A129994 2
A129999 A129996 3
A129996 A129994 1
A129999 A129996 2
A129999 A129996 1
B129994 B129993 1
B129996 B129994 2
B129999 B129996 3
B129996 B129994 1
B129999 B129996 2
B129999 B129996 1
C129994 C129993 1
C129996 C129994 2
C129996 C129994 1
The result I would like to have is just one chain for each set - however my issue is that I have no logical record for a START WITH clause.
A129994 A129993 1
A129996 A129994 2
A129999 A129996 3
B129994 B129993 1
B129996 B129994 2
B129999 B129996 3
C129994 C129993 1
C129996 C129994 2
There is no specific record type that would be a start point such as a null parent, and the ID's used are in no logical order so I can't use the min value or a group b. Any pointers gratefully received.
Edited by: jweb on 12-Jul-2012 04:51