Page 1 of 1

Any default function for finding hierarchies ?

Posted: Thu Nov 22, 2012 7:44 am
by abeeshdreams
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?

Re: Any default function for finding hierarchies ?

Posted: Fri Nov 23, 2012 3:29 pm
by id10t
Hi!
Is there any way to get the hierarchy in the same table other than using UDF?
No! SQL do not supports recursive relations.
How can I do it without using UDF?
So far - you can't. Ask for feature request and wait when it will migrate from GitHub to Vertica's core. IMHO - chance very small and sometimes it's problem when customer don't want anything external like gcc for compiling UDF.

Re: Any default function for finding hierarchies ?

Posted: Tue Nov 27, 2012 3:11 pm
by jpcavanaugh
Oracle supports hierarchies in data via the connect_by http://psoug.org/reference/connectby.html construct. Vertica does not have this functionality in the base release but there is an extension in github https://github.com/vertica/Vertica-Exte ... _functions that mimics this.