Portal

Hello,

we have some hierarchical data at 1 or 2, or 3 levels in our database, using 3 differents tables : Organization -> Department -> Units.

1 Organization => 0/ N Department

1 Department => 0/N Unit

We need to view :

all Organizations with or without Departments

 and all departments with or without units.

IN SQL the query would be:

select

gd_organization.official_name

,gd_department.label

,gd_unit.label

from

            dvt_data_location.gd_organization

            LEFT JOIN dvt_data_location.gd_department ON gd_department.f_organization = gd_organization.code

            LEFT JOIN dvt_data_location.gd_unit gd_unit ON gd_unit.f_department = gd_department.department_id

            where 1=1

            ;

I can't manage to display the result of this SQL in a business view, does someone would have a solution for this case ?

NB if the relationships were

1 Organization => 1/ N Department

1 Department => 1/N Unit

it would be easily resolved using a business View based on unit, but if I do that whith our current model I don't see all the departments that are not linked to any units