All Forums Database
SusanPhilip 3 posts Joined 10/15
13 Oct 2015
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

dnoeth 4628 posts Joined 11/04
13 Oct 2015

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?

Dieter

SusanPhilip 3 posts Joined 10/15
14 Oct 2015

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.

VandeBergB 182 posts Joined 09/06
15 Oct 2015

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 

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
15 Oct 2015

Can you show your current query?

Dieter

SusanPhilip 3 posts Joined 10/15
16 Oct 2015

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;

You must sign in to leave a comment.