recursive query numeric overflow issue

Database
Enthusiast

recursive query numeric overflow issue

Hi All,

I am using recursive query to fetch the top most hireracy of the relation. I have data as shown below:

Manager Reportee Department

1             2             ABC 

2             3             ABC 

3             4             ABC 

3             1             ABC 

4             5             ABC 

Here 4 is manager of 5, 3 is manager of 4, 2 is manager of 3, 1 is manager of 2. So the top most level manager is 1.

I am using recursive script to acheive this. But since i am having one more record saying 3 is manger of 1, it gets into infinite loop :(.

I need to stop the loop if the manager id comes again in the loop, in this sceanerio 3 comes twice in manager id. I need to have a check for re-occurance of the manager id in the loop and exit it out.

PFB the recursive query i have used:

with recursive recursive_T3(manager,reportee,loopno)

as

(

sel B.manager ,

B.reportee ,

0

from DB_TEST.T3 B

where B.B = 5

union all

sel B.manager ,

T2.reportee ,

T2.loopno+1

from DB_TEST.T3 B, recursive_T3 T2

where B.B = T2.manager

)

sel * from recursive_T3 where loopno = (sel max(loopno) from recursive_T3);

Kindly help to sort this out.

--Arun

4 REPLIES
Teradata Employee

Re: recursive query numeric overflow issue

Hello Arun,

if the recursion is not too deep, then one of the possible options is to store all the "previous" manager ids in a separate column, and then filter by that column, like this:

 

with recursive recursive_T3(manager,reportee,loopno, manager_list)

as

(

sel B.manager ,

B.reportee ,

0,

'('||trim(b.manager)||')' as manager_list

from T3 as B

where B.reportee = 5

union all

sel B.manager ,

T2.reportee ,

T2.loopno+1,

T2.manager_list || '('||trim(b.manager)||')' as manager_list

from T3 as B, recursive_T3 T2

where B.reportee = T2.manager

and position ( '('||trim(B.manager)||')'  IN  T2.manager_list  ) =0

and T2.loopno <= 100

)

sel * from recursive_T3 where loopno = (sel max(loopno) from recursive_T3);


Regards,

Vlad.

Enthusiast

Re: recursive query numeric overflow issue

Hi VBurmist,

It is working :).

Thanks a lot.

--Arun

Enthusiast

Re: recursive query numeric overflow issue

Hi Experts,

Kindly help me to achieve the scenario in Teradata stored procedure.

Thank you!

--Arun

Enthusiast

Re: recursive query numeric overflow issue

Hi,

Stored procedure has loop, cursor,do while,if else etc condition.

There are lots of sample stored procedures in net.

Examples below are some snippets of code

CREATE PROCEDURE abc (

OUT parm1 INTEGER,

IN parm2 CHAR(13),

......

)

DECLARE hNumber INTEGER;

L1: LOOP

---Your select 

IF (CHECKING) THEN

LEAVE L1;

END IF;

END LOOP L1;

FOR emp1 AS emp_det CURSOR FOR

your select ....

DO

Your DML...

END FOR;

You can think of a nested loop and check the condition.

You can call  a stored procedure recursively.

Maybe you can try so that you can get the feel.

Cheers,

Raja