fbpx

Problem-solving without a Teradata Recursive Query

In our example, we want to represent a corporate hierarchy. Without recursion, this can be done, e.g., with the query shown 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
;

Above query has several negative properties:

  • The number of hierarchy levels must be known.
    If too few levels are taken into account, 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
;

Above recursive query has several benefits:

  • Only the required number of recursive join steps are executed
  • The number of levels can be limited to avoid “runaway” recursions

The only change might be needed to increase the size of column Company_Hierarchy (in the above example set to VARCHAR(1024))

Performance Considerations

Which of the two queries has the better performance cannot be said in general. It depends on the data demography. Our suggestion is to choose the recursive variant in principle since it is more flexible. If performance is an essential factor, we recommend measuring the resource consumption in table DBC.DBQLOGTBL for both variants and then decide accordingly.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy now at Amazon
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>