Any default function for finding hierarchies ?

Moderator: NorbertKrupa

Post Reply
User avatar
abeeshdreams
Newbie
Newbie
Posts: 7
Joined: Tue Jul 10, 2012 6:11 am

Any default function for finding hierarchies ?

Post by abeeshdreams » 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?
Abeeshdreams

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Any default function for finding hierarchies ?

Post by id10t » Fri Nov 23, 2012 3:29 pm

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.

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Any default function for finding hierarchies ?

Post by jpcavanaugh » Tue Nov 27, 2012 3:11 pm

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.

Post Reply

Return to “Vertica Analytics”