All Forums Database
Arun1377 11 posts Joined 09/13
20 Nov 2013
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
 
 

 

VBurmist 96 posts Joined 12/09
20 Nov 2013

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.

Arun1377 11 posts Joined 09/13
21 Nov 2013

Hi VBurmist,
It is working :).
Thanks a lot.
--Arun

Arun1377 11 posts Joined 09/13
01 Jan 2014

Hi Experts,
 
Kindly help me to achieve the scenario in Teradata stored procedure.
 
Thank you!
 
--Arun

Raja_KT 1246 posts Joined 07/09
01 Jan 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.