How implementing an hierarchy without using a recursive CTE

Hi!

In case you have limited number of hierarchy levels and have some nullable kind of parent field, you can use the approach I used for "flattening" list of the organizational units:

SELECT
org_units.id_org_unit
, org_units.id_parent
, org_units.org_unit_code
, org_units.org_unit
, org_unit_hierarchy.top_unit_code
, org_unit_hierarchy.top_unit
, org_unit_hierarchy.middle_unit_code
, org_unit_hierarchy.middle_unit
, org_unit_hierarchy.low_unit_code
, org_unit_hierarchy.low_unit
FROM org_units
INNER JOIN (
SELECT
base_unit.id_org_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN third_unit.org_unit_code
WHEN second_unit.org_unit_code IS NOT NULL THEN second_unit.org_unit_code
ELSE base_unit.org_unit_code
END AS top_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN third_unit.org_unit
WHEN second_unit.org_unit IS NOT NULL THEN second_unit.org_unit
ELSE base_unit.org_unit
END AS top_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN second_unit.org_unit_code
WHEN second_unit.org_unit_code IS NOT NULL THEN base_unit.org_unit_code
END AS middle_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN second_unit.org_unit
WHEN second_unit.org_unit IS NOT NULL THEN base_unit.org_unit
END AS middle_unit
, CASE
WHEN third_unit.org_unit_code IS NOT NULL THEN base_unit.org_unit_code
END AS low_unit_code
, CASE
WHEN third_unit.org_unit IS NOT NULL THEN base_unit.org_unit
END AS low_unit
FROM org_units AS base_unit
LEFT OUTER JOIN org_units AS second_unit ON base_unit.id_parent = second_unit.id_org_unit
LEFT OUTER JOIN org_units AS third_unit ON second_unit.id_parent = third_unit.id_org_unit
) AS org_unit_hierarchy