REcursive process

Database

REcursive process

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

5 REPLIES
Senior Apprentice

Re: REcursive process

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?

Re: REcursive process

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.

Enthusiast

Re: REcursive process

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

    CREATE RECURSIVE VIEW reachable_from (source,destination,depth) AS (

      SELECT root.source, root.destination, 0 AS depth <--- '0 as depth' initiates the counter

      FROM flights AS root

      WHERE root.source = 'Paris'

    UNION ALL

      SELECT in1.source, out1.destination, in1.depth + 1  <--- this line increments the recursion counter

      FROM reachable_from AS in1, flights AS out1

      WHERE in1.destination = out1.source

      AND   in1.depth <= 100);  <--- this line limits the number of recursion loops 

Senior Apprentice

Re: REcursive process

Can you show your current query?

Re: REcursive process

WITH RECURSIVE RELSHP (Parnt_part_nbr,Part_nbr,depth) AS (

      SELECT root.Parnt_part_nbr, root.Part_nbr, 1 AS depth

      FROM bom   root

    UNION ALL

      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;