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 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.