Query Needed

UDA
Enthusiast

Query Needed

Sample Input :
child_node_ID IDcode parent_node_ID
1 C50881-002 1
2 C50881-006 2
3 C50881-007 3
4 C50881-006 2
5 C50881-007 3
6 C97663-001 6
7 C97663-002 6
8 C97663-003 8
9 C97663-002 7
10 C97673-002 9

I have data coming in format shown above, i need output as shown below.
The output required is the lvl cout - the calculation for lvl_cnt is -
Suppose we have given input child_node_ID=10 then i need to see corresponding parent_node_id, here it is 9...
Now i need to go to child_node_ID = 9 and check it parent_node_id, which is 7 ..
Now i need to go to child_node_ID = 7 and check it parent_node_id, which is 6 ..
Now i need to go to child_node_ID = 6 and check it parent_node_id, which is 6 .. Here child_node_ID = parent_node_id...
Hence the lvl_cnt =3 as we have passed through 3 levels to make them equal.

Suppose i consider child_node_ID = 1, its parent_node_id =1...hence lvl_cnt =0 .

Output :
child_node_ID IDcode parent_node_ID lvl_cnt
1 C50881-002 1 0
2 C50881-006 2 0
3 C50881-007 3 0
4 C50881-006 2 1
5 C50881-007 3 1
6 C97663-001 6 0
7 C97663-002 6 1
8 C97663-003 8 0
9 C97663-002 7 2
10 C97673-002 9 3

Mostly this can we solved by recursive query or while loop in teradata.

Can anyone help me in solving this issue.

Regards,
Prashanth Veerabomma.
3 REPLIES
Enthusiast

Re: Query Needed

Create Volatile Table HTbl
( Child_Node_Id Smallint Not Null
, IDcode Char(12) Not Null
, Parent_Node_ID Smallint Not Null)
Primary Index (IDcode)
On Commit Preserve Rows
;
Insert Into HTbl Values (1,'C50881-002',1);
Insert Into HTbl Values (2,'C50881-006',2);
Insert Into HTbl Values (3,'C50881-007',3);
Insert Into HTbl Values (4,'C50881-006',2);
Insert Into HTbl Values (5,'C50881-007',3);
Insert Into HTbl Values (6,'C97663-001',6);
Insert Into HTbl Values (7,'C97663-002',6);
Insert Into HTbl Values (8,'C97663-003',8);
Insert Into HTbl Values (9,'C97663-002',7);
Insert Into HTbl Values (10,'C97673-002',9);

Select * From HTbl Order By 1,2,3
;

With Recursive Hierchy (Child_Node_Id , IDCode , Parent_Node_Id , Level)
AS (Select Child_Node_Id , IDCode , Parent_Node_Id, 0
From HTbl
Where Child_Node_Id = Parent_Node_Id
UNION ALL
Select Hie.Child_Node_Id
, Hie.IDcode
, Hie.Parent_Node_Id
, Level + 1
From Hierchy Hier
Join HTbl Hie
On Hier.Child_Node_Id = Hie.Parent_Node_Id
)
Select * From Hierchy
Order By 1,2,3,4
;

Gives: -----------------------------------------------------
Child_Node_Id IDCode Parent_Node_Id Level
1 C50881-002 1 0
2 C50881-006 2 0
3 C50881-007 3 0
4 C50881-006 2 1
5 C50881-007 3 1
6 C97663-001 6 0
7 C97663-002 6 1
8 C97663-003 8 0
9 C97663-002 7 2
10 C97673-002 9 3
Enthusiast

Re: Query Needed

Thanks Man, But i find the query is running for long time, i dont know whether this is environment problem......How long is it taking to give the output?
Enthusiast

Re: Query Needed

With the test data, less than one second (on Express edition).
If you are using real data, what is the primary index on the source table. I used a NUPI on IDcode - anything else could take a while longer.