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?
Any default function for finding hierarchies ?
Moderator: NorbertKrupa
- abeeshdreams
- Newbie
- Posts: 7
- Joined: Tue Jul 10, 2012 6:11 am
Any default function for finding hierarchies ?
Abeeshdreams
Re: Any default function for finding hierarchies ?
Hi!
No! SQL do not supports recursive relations.Is there any way to get the hierarchy in the same table other than 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.How can I do it without using UDF?
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Any default function for finding hierarchies ?
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.