Is there an alternative way to achieve recursion in teradata other than using "WITH RECURSIVE ".
The objective is to get the hierarchy information from a dataset which is known to have multiple levels
If the number of levels is known, small & fixed you can utilize one left join per level.
Otherwise eecursion can be replaced by a WHILE loop in a Stored Procedure, but why would you want that?
my requirement is to get a part number from a table along with the parent part number and the level at which the the part is associated with the parent. I tried the recursive query but it is spooling out and so i am looking at an alternative option.
A common error that results in recursive queries/views spooling out is the missing "depth" limiter. without the depth column and the where depth <= 100 it doesn't know where to stop and consequently hits the spool limit
WITH RECURSIVE RELSHP (Parnt_part_nbr,Part_nbr,depth) AS (
SELECT root.Parnt_part_nbr, root.Part_nbr, 1 AS depth
FROM bom root
SELECT a.Parnt_part_nbr, par.Part_nbr, a.depth + 1
FROM RELSHP a , bom par
WHERE a.Part_nbr = par.Parnt_part_nbr
AND a.depth <= 100)
select * from RELSHP a;