Hi Experts,
I am trying to find the logic which best suits my requirement:-
Version Oracle 19C
An external API wants to get hierarchy based on name characters passed .
Table1 has data related to Primary ID and Name
Table2 has data related to hierarchy (primary ID is used there, similar to employee manager but at 3 levels)
The solution i thought was , we get name characters and get all primary IDs for that in one procedure (ref cursor to be returned)
Pass these IDs to another SP which returns result set as ref cursor based on hierarchy.
The issue which i see is:
First: My hierarchy getting query is a select with multiple case statement, so i might have to loop through each record to return the ref cursor with data for all IDs which have matching name. Is there any other alternate?
Second: Is it possible to loop through a set of records and combine the result in one ref cursor?
Third: I think the UI might run slow when name parameters are passed.
Sorry its not straight forward query like I normally posted, with all create table statements and data statements.
Happy to answer any doubts related to the requirements.
Thanks,
Nik
CREATE TABLE hier ( L1 varchar2(10), L2 VARCHAR2(10), L3 varchar2(10));
CREATE TABLE hier_name ( L_level varchar2(10), L_level_name varchar2(50));
insert into hier_name values ('A1', 'Desc for A1');
insert into hier_name values ('A2', 'Desc for A1');
insert into hier_name values ('A3', 'Desc for A1');
insert into hier_name values ('A4', 'Desc for A1');
insert into hier
(L1,L2,L3)
Values
(A1,A1,A1);
insert into hier
(L1,L2,L3)
Values
(A1,A1,A3);
insert into hier
(L1,L2,L3)
Values
(A1,A1,A4);
insert into hier
(L1,L2,L3)
Values
(A1,A2,A5);
Required output:
COlumns :- <level> , <name> , <L1_parent_Name>, <L2_Parent_name>
--SO basically A1 is the parent and it is child at both levels,
for e.g.
If we pass A1
it gives
COlumns :- <level> , <name> , <L1_parent_Name>, <L2_Parent_name>
A1, 'Descr for A1', A1,A1
If we pass A3
A3,'Desc for A3',A1,A1
If we pass A5
A5,'Desc for A5',A1,A2
The input from API would be L_level_name (we match contains i.e. like with %<input>%)
So approach is to get all L_level from hier_name table and then find hierarchy to return the resultset
Solution which I though for:-
Hierarchy Query:-
Even before running this Level, i will have to find all l_level for name character match (ref cursor) then loop for each record to find level (depth) for that record using query below
--FIrst find level of input passed
select distinct
case
when L1 = <input> then 'L1'
when L2 = <input> then 'L2'
else 'L3'
from hier
where
( L1 = <input>)
or
( L2 = <input>)
or
( L3 = <input>);
Insert all below records in Global temporary table
select distinct <level from above query>, L_level_name ,
case
when <level> = 'L1'
then <input>
when <level> = 'L2>
then <level>
else
end h.L2 As "parent_level2" (dont need desc),
case
when <level> = 'L1'
then <input>
when <level> = 'L2>
then h.L1
else
end h.L1 As "parent_level1" (dont need desc)
from
hier h,
hier_name nm
where 1=1
and (case when <level> = 'L1' then h.L1
when <level> = 'L2' then h.L2
else h.L3
end ) = nm.L_level
Return ref cursor for above records