Hi All
Background:
A table stores links between Incidents and different Problem Mgmt task types or between 2 different task types. Link is stored as source and destination. So for many cases source/destination (destination/source) can not be considered as parent/child (child/parent). But tasks are always directly or indirectly related to an incident
Problem:
I need to get all possible relations of all related tasks to Incident as well as intermediate parents.
sample Scenario:
Expected Result:
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/C71HXTZD3N9O/image.png" alt="image.png">drop table item_links purge;
drop table items purge;
drop table item_type purge;
drop table item_linktype purge;
create table item_linktype
(id integer primary key,
link_name varchar2(50));
insert into item_linktype values(300,'CA');
insert into item_linktype values(301,'Problem');
insert into item_linktype values(400,'Problem Mgmt - Tasks');
create table item_type
( id integer primary key,
type_name varchar2(50));
insert into item_type values(900,'Incident');
insert into item_type values(901,'Problem Mgmt');
insert into item_type values(902,'Problem Mgmt - CA');
insert into item_type values(903,'Problem Mgmt - Analysis');
insert into item_type values(904,'Problem Mgmt - ES');
insert into item_type values(905,'Problem Mgmt - RCA');
create table items
(id integer primary key,
item_type_id integer,
constraint items_fk01 foreign key (item_type_id) references item_type(id));
insert into items values (10206,900);
insert into items values (10225,901);
insert into items values (9203,900);
insert into items values (12920,902);
insert into items values (12921,902);
insert into items values (9244,901);
insert into items values (10226,903);
insert into items values (10227,904);
insert into items values (9246,904);
insert into items values (9245,903);
insert into items values (9247,905);
insert into items values (1628,901);
insert into items values (1711,900);
insert into items values (5776,902);
insert into items values (1631,902);
insert into items values (4753,900);
insert into items values (4858,901);
insert into items values (1629,903);
insert into items values (7642,902);
insert into items values (7648,902);
insert into items values (4859,903);
insert into items values (4860,904);
create table item_links
(id integer primary key,
link_type_id integer,
source integer,
dest integer,
constraint item_links_fk01 foreign key (link_type_id) references item_linktype(id),
constraint item_links_fk02 foreign key (source) references items(id),
constraint item_links_fk03 foreign key (dest) references items(id));
insert into item_links values (2817,301,10206,10225);
insert into item_links values (2818,400,10225,10226);
insert into item_links values (2009,400,10225,10227);
insert into item_links values (2919,301,9203,9244);
insert into item_links values (2991,300,12920,9244);
insert into item_links values (2992,300,12920,9203);
insert into item_links values (2993,300,12921,9244);
insert into item_links values (9291,400,9244,9246);
insert into item_links values (2920,400,9244,9245);
insert into item_links values (2922,400,9244,9247);
insert into item_links values (2971,400,1628,1629);
insert into item_links values (2970,301,1711,1628);
insert into item_links values (3341,300,5776,1628);
insert into item_links values (2972,300,1631,1629);
insert into item_links values (907,301,4753,4858);
insert into item_links values (85,301,4858,7642);
insert into item_links values (86,301,4858,7648);
insert into item_links values (908,400,4858,4859);
insert into item_links values (909,400,4858,4860);
Commit;
-- Below query is source of sample scenario shown above
SELECT
item_links.id,
item_linktype.link_name linktype,
item_links.source,
st.type_name source_type,
item_links.dest,
dt.type_name dest_type
FROM
item_links,
item_linktype,
items s,
item_type st,
items d,
item_type dt
WHERE
item_links.link_type_id = item_linktype.id
AND item_links.source = s.id
AND s.item_type_id = st.id
AND item_links.dest = d.id
AND d.item_type_id = dt.id;
I tried to get result using hierarchical query but it gives a lot of extra records and also on full data set it runs forever and
Any help is greatly appreciated.
Thanks Sud