Problem-solving without a Teradata Recursive Query
To depict a corporate hierarchy in our instance, we can employ a non-recursive approach, illustrated by the query presented below:
SELECT -- Concatenation of all Companies CASE WHEN Level >= 3 THEN TRIM(t03.Company_Id) || '.' ELSE '' END || CASE WHEN Level >= 2 THEN TRIM(t02.Company_Id) || '.' ELSE '' END || TRIM(t01.Company_Id) AS Company_Hierarchy -- Level Calculation ,CASE NULL WHEN t01.Parent_Company_Id THEN 1 WHEN t02.Parent_Company_Id THEN 2 WHEN t03.Parent_Company_Id THEN 3 END AS Level FROM DWHPro.Company t01 LEFT OUTER JOIN DWHPro.Company B ON t01.Parent_Company_Id = t02.Company_Id LEFT OUTER JOIN DWHPro.Company t03 ON t02.Parent_Company_Id = t03.Company_Id ORDER BY 1 ;
The above query has several harmful properties:
- We must know the number of hierarchy levels.
If too few levels are considered, the query returns incorrect results. If too many hierarchy levels are considered, unnecessary joins are caused.
- If the number of hierarchy levels changes, the following changes to the SQL statement are necessary:
– Joins need to be added or removed
– One CASE Statement has to be adjusted to determine the levels
– One CASE Statement has to be adjusted to calculate the Hierarchy
Problem-solving with a Teradata Recursive Query
WITH RECURSIVE Hierarchy (Company_Hierarchy, Company_Id, Level) AS ( -- SELECT TRIM(Company_Id) (VARCHAR(1024)) AS Company_Hierarchy , Company_Id , 1 AS Level FROM DWHPro.Org WHERE Parent_Company_Id IS NULL UNION ALL SELECT t02.Company_Hierarchy || '.' || TRIM(t01.Company_Id) AS Company_Hierarchy , t01.Company_Id , t02.Level + 1 AS Level FROM DWHPro.Org t01 ,Hierarchy t02 WHERE t01.Parent_Company_Id = t02.Company_Id AND Level <= 10 -- limit to avoid infinite loop errors ) SELECT Company_Hierarchy, Company_Id, Level FROM Hierarchy ORDER BY 1 ;
The above recursive query has several benefits:
- Only the required number of recursive join steps are executed
- We can limit the number of levels to avoid “runaway” recursions
The only modification required to enlarge the Company_Hierarchy column (previously defined as VARCHAR(1024)) is to adjust its size.
The superiority of either query cannot be determined universally; it varies based on the characteristics of the data. We generally advise opting for the recursive option as it is more adaptable. If efficiency is a crucial consideration, we suggest evaluating the resource utilization in Table DBC.DBQLOGTBL for both options and making a decision based on the results.