I have issue in my Output. Anyone Please assist to me. I want hierarchical tree structure for two tables in Report level query in Oracle APEX(Version 22.1.5). I have attached below for exact OUPUT(Which I need).. Also I have given all the details in below for tables, datas, need exact O/P Screenshot and my tried code..
I have two tables as ORGANIZATION_UNITS_T and POSITIONS_T..
TABLE 1 (ORGANIZATION_UNITS_T) :
CREATE TABLE "ORGANIZATION_UNITS_T"
( "ORG_UNIT_ID" NUMBER,
"ORG_UNIT_NAME" VARCHAR2(300),
"PARENT_ORGUNIT_ID" NUMBER,
"ENTITY_TYPE_ID" NUMBER,
PRIMARY KEY ("ORG_UNIT_ID")
);
With this datas in below screenshot in TABLE 1:
TABLE 2 (POSITIONS_T) :
CREATE TABLE "POSITIONS_T"
("POSITION_ID" NUMBER,
"POSITION_NAME" VARCHAR2(300),
"ORGUNIT_ID" NUMBER,
PRIMARY KEY ("POSITION_ID")
);
With this datas in below screenshot in TABLE 2:
I want Hierarchical tree structure in Oracle APEX(22.1.5) for both the tables…
I want output like below:
Level Title
1 Govt.of Tamil Nadu
2 |--> Chief Minister
3 Chief Secretary
4 |-- > TestN
5 Rural and Pachayat Raj
6 |-- > Assistant Manager
|-- > JD
Here below is my tried code in Oracle APEX :
select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
"POSITION_NAME " as title,
null as icon,
"ORG_UNIT_ID" as value,
null as tooltip,
null as link
from "#OWNER#"."EG_OFFICE_POSITIONS_V"
start with "PARENT_ORGUNIT_ID" is null
connect by prior "ORG_UNIT_ID" = "PARENT_ORGUNIT_ID"
order siblings by "POSITION_NAME "
I don't know what is mistake in my above tried code. I did not get exact output like tree structure…
Also I have VIEW as ORGANIZATION_UNITS_V…
Here below is my ORGANIZATION_UNITS_V…..
CREATE OR REPLACE FORCE EDITIONABLE VIEW "ORGANIZATION_UNITS_V
("ORG_UNIT_ID", "ORG_UNIT_NAME", "ORG_UNIT_SHORTNAME", "PARENT_ORGUNIT_ID",
"ENTITY_TYPE_ID", "ENTITY_NAME", "HIERARCHY") AS
select EOU.ORG_UNIT_ID,
EOU.ORG_UNIT_NAME,
EOU.ORG_UNIT_SHORTNAME,
EOU.PARENT_ORGUNIT_ID,
EOU.ENTITY_TYPE_ID,
EET.ENTITY_NAME,
EET.HIERARCHY
from ORGANIZATION_UNITS_T EOU,
ENTITY_TYPES_T EET
where EOU.ENTITY_TYPE_ID = EET.ENTITY_TYPE_ID
and EOU.STATUS = 'Y'
order by EET.HIERARCHY, EOU.ORG_UNIT_NAME
with this data in it:
insert into ORGANIZATION_UNITS_V values
('5','Govt.of Tamil Nadu','GOTN','-','1','Govt. of Tamil Nadu','10');
insert into ORGANIZATION_UNITS_V values
('6','Chief Secretary','CS','5','5','Secretariat','20');
insert into ORGANIZATION_UNITS_V values
('7','Rural and Pachayat Raj','RD','6','2','Department','30');
insert into ORGANIZATION_UNITS_V values
('12','Information Technology and Digital Services','IT&DS','6','2','Department','30');
insert into ORGANIZATION_UNITS_V values
('17','Electronics Corporation of Tamilnadu','ELCOT','12','3','Directorate','40');
insert into ORGANIZATION_UNITS_V values
('16','Tamil Arasu Cable Operator ','TACO','12','3','Directorate','40');
insert into ORGANIZATION_UNITS_V values
('31','Directorate of e-Governance','DOEG','14','7','Wing','50');
Below Code I am tried in another way Using ORGANIZATION_UNITS_V VIEW and POSITIONS_T table:
with
prep (c, p, name) as (
select org_unit_id, parent_orgunit_id, org_unit_name
from organization_units_v
union all
select null, orgunit_id, '-- ' ||position_name
from positions_t
)
select rpad(' ', 2 * (level - 1)) || name as name,
c as node_value,
level - 1 as hierarchy_level,
case when c is not null then 1 else 2 end as node_status_number
from prep p
start with name = 'Govt. of Tamil Nadu'
connect by p = prior c
order siblings by p.name;
Below Screenshot is SQL OUTPUT for above code in SQL Commands:
Above code got Output in SQL commands.. But I did not get Output in Frontend(Oracle APEX).. It got some JS errors.. Can anyone please resolve my issue.…
Below screenshot is JS error with Blank O/P in Frontend(Oracle APEX)
Below screenshot for exactly I need like Organization and Postion Tree structure in Oracle APEX.
I want like, title respective position, like a tree structure, in oracle APEX..
If I click ORG_UNIT_NAME it will show POSITION_NAME under ORG_UNIT_NAME like above attached screenshot tree structure in Oracle APEX…
Can anyone please resolve my issue.. Thanks in Advance..