Create view of departments with a specific structure

Question

I have an Oracle table with the following structure of departments:

cddepartment - NUMBER - unique numeric identifier for department
iddepartment - VARCHAR - unique string identifier for department
nmdepartment - VARCHAR - name of department
cddeptowner - NUMBER - numeric identifier of department's parent (higher level)

The departments'names tree, in a visual form, looks something like this:

DIRECCIÓN 1
----GERENCIA 1
--------SUBGERENCIA 1
------------JEFATURA 1
DIRECCION 2
----GERENCIA 2
--------SUBGERENCIA 2
------------JEFATURA 2
GERENCIA 3
----SUBGERENCIA 3
--------JEFATURA 3
GERENCIA 4
----SUBGERENCIA 4
DIRECCIÓN 5
----SUBGERENCIA 5
--------JEFATURA 5
...
...
DIRECCIÓN N
----GERENCIA N
--------SUBGERENCIA N
------------JEFATURA N

Where all sub-trees dont necessarily have the same exact structure. Some of them can be DIRECCION-GERENCIA-SUBGERENCIA-JEFATURA. Some of them can be GERENCIA-SUBGERENCIA-JEFATURA, some DIRECCION-SUBGERENCIA-JEFATURA, and almost 10 different combinations of the four basic units: DIRECCIÓN, GERENCIA, SUBGERENCIA and JEFATURA.

The only fixed rule in a subtree context is that DIRECCION is always at a higher level than GERENCIA, GERENCIA at a higher level than SUBGERENCIA, and SUBGERENCIA at a higher level than JEFATURA. And a subtree can be formed by two, three or four basic units.

So, I need is to create a view (in Oracle) with five fixed columns as in the imagen below. The goal is to easily query a department's DIRECCION, GERENCIA, SUBGERENCIA and/or JEFATURA from an SQL report.

DEPARTMENT----DIRECCION----GERENCIA----SUBGERENCIA----JEFATURA
--------------------------------------------------------------------------
JEFATURA 1    DIRECCIÓN 1  GERENCIA 1  SUBGERENCIA 1  JEFATURA 1
SUBGERENCIA 1 DIRECCIÓN 1  GERENCIA 1  SUBGERENCIA 1
GERENCIA 1    DIRECCIÓN 1  GERENCIA 1
DIRECCIÓN 1   DIRECCIÓN 1
JEFATURA 2    DIRECCIÓN 2  GERENCIA 2  SUBGERENCIA 2  JEFATURA 2
SUBGERENCIA 2 DIRECCIÓN 2  GERENCIA 2  SUBGERENCIA 2
GERENCIA 2    DIRECCIÓN 2  GERENCIA 2
DIRECCIÓN 2   DIRECCIÓN 2
JEFATURA 3                 GERENCIA 3  SUBGERENCIA 3  JEFATURA 3
SUBGERENCIA 3              GERENCIA 3  SUBGERENCIA 3
GERENCIA 3                 GERENCIA 3
SUBGERENCIA 4              GERENCIA 4  SUBGERENCIA 4
GERENCIA 4                 GERENCIA 4
JEFATURA 5   DIRECCIÓN 5               SUBGERENCIA 5  JEFATURA 5
SUBGERENCIA5 DIRECCIÓN 5               SUBGERENCIA 5
DIRECCIÓN 5  DIRECCIÓN 5
...
...
JEFATURA N    DIRECCIÓN N  GERENCIA N  SUBGERENCIA N  JEFATURA N
SUBGERENCIA N DIRECCIÓN N  GERENCIA N  SUBGERENCIA N
GERENCIA N    DIRECCIÓN N  GERENCIA N
DIRECCIÓN N   DIRECCIÓN N

I appreciate any clue on how i can achieve this. Thanks in advance!

Answer 1

Have a look at hierarchical queries ( eg here or here ). Suppose you create a table as follows:

create table dgsj -- dgsj: DIRECCION, GERENCIA, SUBGERENCIA, JEFATURA
as
select 1 cddepartment, 'DIRECCION_1' nmdepartment, NULL cddeptowner from dual union all
select 2, 'GERENCIA_1',    1 from dual union all
select 3, 'SUBGERENCIA_1', 2  from dual union all
select 4, 'JEFATURA_1',    3  from dual union all
select 5, 'DIRECCION_2',   NULL from dual union all
select 6, 'GERENCIA_2',    5 from dual union all
select 7, 'SUBGERENCIA_2', 6  from dual union all
select 8, 'JEFATURA_2',    7  from dual union all
select 9, 'GERENCIA_3',    NULL from dual union all
select 10, 'SUBGERENCIA_3', 9  from dual union all
select 11, 'JEFATURA_3',    10  from dual union all
select 12, 'GERENCIA_4',    NULL from dual union all
select 13, 'SUBGERENCIA_4', 12  from dual union all
select 14, 'DIRECCION_5',    NULL from dual union all
select 15, 'SUBGERENCIA_5', 14  from dual union all
select 16, 'JEFATURA_5',    15  from dual ;

Easier to read:

select * from dgsj;

CDDEPARTMENT  NMDEPARTMENT   CDDEPTOWNER  
1             DIRECCION_1    NULL         
2             GERENCIA_1     1            
3             SUBGERENCIA_1  2            
4             JEFATURA_1     3            
5             DIRECCION_2    NULL         
6             GERENCIA_2     5            
7             SUBGERENCIA_2  6            
8             JEFATURA_2     7            
9             GERENCIA_3     NULL         
10            SUBGERENCIA_3  9            
11            JEFATURA_3     10           
12            GERENCIA_4     NULL         
13            SUBGERENCIA_4  12           
14            DIRECCION_5    NULL         
15            SUBGERENCIA_5  14           
16            JEFATURA_5     15 

Query

The following query returns all department names and "paths" that represent the hierarchy, along with ids, levels, and parent ids.

select 
  cddepartment as id
, nmdepartment
, sys_connect_by_path( nmdepartment, '/' ) full_path
, level as lvl
, case 
   when cddeptowner is null then '-' 
   else to_char( cddeptowner )
  end parent
from dgsj
start with cddeptowner is null
connect by cddeptowner = prior cddepartment 
order by level, cddeptowner, cddepartment ;

-- result
ID  NMDEPARTMENT   FULL_PATH                                         LVL  PARENT  
1   DIRECCION_1    /DIRECCION_1                                      1    -       
5   DIRECCION_2    /DIRECCION_2                                      1    -       
9   GERENCIA_3     /GERENCIA_3                                       1    -       
12  GERENCIA_4     /GERENCIA_4                                       1    -       
14  DIRECCION_5    /DIRECCION_5                                      1    -       
2   GERENCIA_1     /DIRECCION_1/GERENCIA_1                           2    1       
6   GERENCIA_2     /DIRECCION_2/GERENCIA_2                           2    5       
10  SUBGERENCIA_3  /GERENCIA_3/SUBGERENCIA_3                         2    9       
13  SUBGERENCIA_4  /GERENCIA_4/SUBGERENCIA_4                         2    12      
15  SUBGERENCIA_5  /DIRECCION_5/SUBGERENCIA_5                        2    14      
3   SUBGERENCIA_1  /DIRECCION_1/GERENCIA_1/SUBGERENCIA_1             3    2       
7   SUBGERENCIA_2  /DIRECCION_2/GERENCIA_2/SUBGERENCIA_2             3    6       
11  JEFATURA_3     /GERENCIA_3/SUBGERENCIA_3/JEFATURA_3              3    10      
16  JEFATURA_5     /DIRECCION_5/SUBGERENCIA_5/JEFATURA_5             3    15      
4   JEFATURA_1     /DIRECCION_1/GERENCIA_1/SUBGERENCIA_1/JEFATURA_1  4    3       
8   JEFATURA_2     /DIRECCION_2/GERENCIA_2/SUBGERENCIA_2/JEFATURA_2  4    7  

This will probably need some tweaking, according to your requirements. Tested with Oracle 12c, and Oracle 11g (dbfiddle).

UPDATE (split the full_path into 4 columns, using @David Faber's approach)

select
  nmdepartment 
, nvl( TRIM('/' FROM REGEXP_SUBSTR(full_path, '/DIRECCION_\d+')), '-' )   AS direccion
, nvl( TRIM('/' FROM REGEXP_SUBSTR(full_path, '/GERENCIA_\d+')), '-' )    AS gerencia
, nvl( TRIM('/' FROM REGEXP_SUBSTR(full_path, '/SUBGERENCIA_\d+')), '-' ) AS subgerencia
, nvl( TRIM('/' FROM REGEXP_SUBSTR(full_path, '/JEFATURA_\d+')), '-' )    AS  jefatura
from (
  select 
    cddepartment as id
  , nmdepartment
  , sys_connect_by_path( nmdepartment, '/' ) full_path
  , level as lvl
  , case 
     when cddeptowner is null then '-' 
     else to_char( cddeptowner )
    end parent
  from dgsj
  start with cddeptowner is null
  connect by cddeptowner = prior cddepartment 
  order by level, cddeptowner, cddepartment
) ;

Result

NMDEPARTMENT   DIRECCION    GERENCIA    SUBGERENCIA    JEFATURA    
DIRECCION_1    DIRECCION_1  -           -              -           
DIRECCION_2    DIRECCION_2  -           -              -           
GERENCIA_3     -            GERENCIA_3  -              -           
GERENCIA_4     -            GERENCIA_4  -              -           
DIRECCION_5    DIRECCION_5  -           -              -           
GERENCIA_1     DIRECCION_1  GERENCIA_1  -              -           
GERENCIA_2     DIRECCION_2  GERENCIA_2  -              -           
SUBGERENCIA_3  -            GERENCIA_3  SUBGERENCIA_3  -           
SUBGERENCIA_4  -            GERENCIA_4  SUBGERENCIA_4  -           
SUBGERENCIA_5  DIRECCION_5  -           SUBGERENCIA_5  -           
SUBGERENCIA_1  DIRECCION_1  GERENCIA_1  SUBGERENCIA_1  -           
SUBGERENCIA_2  DIRECCION_2  GERENCIA_2  SUBGERENCIA_2  -           
JEFATURA_3     -            GERENCIA_3  SUBGERENCIA_3  JEFATURA_3  
JEFATURA_5     DIRECCION_5  -           SUBGERENCIA_5  JEFATURA_5  
JEFATURA_1     DIRECCION_1  GERENCIA_1  SUBGERENCIA_1  JEFATURA_1  
JEFATURA_2     DIRECCION_2  GERENCIA_2  SUBGERENCIA_2  JEFATURA_2 

Dbfiddle here.