Any default function for finding hierarchies ?
Posted: Thu Nov 22, 2012 7:44 am
Hi Guys,
Is there any default recursive function in vertica like sys_connect_by_path in Oracle for finding the hierarchy? Is there any way to get the hierarchy in the same table other than using UDF?
My requirement is like this. I have a table as below.
Table X
A | B|C
1 | 5|aa
3 | 4|gh
5 | 7 |rg
4| 0|br
7| 0|ky
Consider col A as child and col B as parent. Now if my input for A is 1 then I have to get the parent of that in B (5), then search for its parent 5(7) and so on till i get parent as 0 and retrieve the value of C(ky) for that row. How can I do it without using UDF?
Is there any default recursive function in vertica like sys_connect_by_path in Oracle for finding the hierarchy? Is there any way to get the hierarchy in the same table other than using UDF?
My requirement is like this. I have a table as below.
Table X
A | B|C
1 | 5|aa
3 | 4|gh
5 | 7 |rg
4| 0|br
7| 0|ky
Consider col A as child and col B as parent. Now if my input for A is 1 then I have to get the parent of that in B (5), then search for its parent 5(7) and so on till i get parent as 0 and retrieve the value of C(ky) for that row. How can I do it without using UDF?