0 Replies Latest reply: Feb 7, 2014 5:07 AM by 81c4b7bc-31d8-4e07-817e-d7153ef0990a RSS

creating report in obiee

81c4b7bc-31d8-4e07-817e-d7153ef0990a Newbie
Currently Being Moderated

CREATE TABLE b(

      ID         VARCHAR2(50 BYTE),

       PARENT_ID  VARCHAR2(50 BYTE),

       NAME       NVARCHAR2(200)

);

 

 

insert into  b values ('401.1.1','401.1','a');

insert into  b values ( '402.1.1','402.1','d');

insert into  b  values ('402.1.1.1','402.1','e');

insert into  b  values ('400','1','asc');

insert into  b  values( '401','400','dep1');

insert into  b  values  ( '402','400','dep2');

insert into  b  values('403','400','dep3');

insert into  b  values('401.1','401','A');

insert into  b  values('401.2','401','B');

insert into  b  values('401.3','401','C');

insert into  b  values('402.1','402','D');

insert into  b  values('402.2','402','E');

 

 

 

NOW I WANT MY RESULT WOULD BE LIKE THIS

 

Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9Header 10Header 11Header 12Header 13Header 14Header 14
4001asc
4001asc401400dep1
4001asc402400dep2
4001asc403400dep3
4001asc401400dep1401.1401A
4001asc401400dep1401.2401B
4001asc401400dep1401.3401C
4001asc402400dep2401.1402D
4001asc402400dep2401.2402E
4001asc401400dep1401.1401A401.1.1401.1a
4001asc402400dep2401.1402D402.1.1402.1d
4001asc402400dep2401.1402D402.1.1402.1d402.1.1.1402.1.1e


i use self join and union all bu retrieve this information ,but in other case my hiearchy tree has more than 10 sub department then my script is not correct ,please give solution not only for 4 subdepartment like this ,but nearly 10 ten subdepartment,because in other offices as i said i have 10 subdepartment ,here i only give example

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points