Im trying to implement a cross reference table and struck in middle. Any help would be highly appreciated please .
Basically i have a data source as below, Chld1 and chld2 can be interchangeable in my source.
I need to select chld1 and chld2 from source and insert into target with a family_id assigned to it.
rule for Family_id :
if a Chld1/chld2 have a match to other chld1/chld2 then those records would be of same family,
if no match then process should assign next sequence as family_id to that record.
sample data and example:-
create table My_source(chld1 number,chld2 number);
Create table My_target(chld1 number,chld2 number,family_id number);
Insert into my_source
With t as
(select 2222 chld1,8888 chld2 from dual union all
select 3333,4444 from dual union all
select 4444,8888 from dual union all
select 3333,8888 from dual union all
select 5555,8888 from dual union all
select 3333,7777 from dual union all
select 1111,9999 from dual
)select * from t;
commit;
o/p:-
Chld1 chld2 family_id
------------------------------------------
2222 8888 1
3333 4444 1
4444 8888 1
3333 8888 1
5555 8888 1
3333 7777 1
1111 9999 2