0

There is one query in oracle which is using CONNECT BY, my requirement is i need to migrate it to Greenplum Sql

I found n tried with recursive class, but getting below error

********** Error **********
ERROR: RECURSIVE option in WITH clause is not supported
SQL state: 0AM00

I didn't get any resolution for below, also let me know/post any query or example where you used with recursive class

Requirement is

I have a table like :

SKey KPI ParentKPI
1 A 0
2 B 1
3 C 2
4 D 3
5 E 4

Need output like below using something similar to Oracle’s connect by:

1 : A
2 : A, B
3 : A, B, C
4 : A, B, C, D
5 : A, B, C, D, E

Is this possible ?

Gurupreet Singh Bhatia
  • 708
  • 6
  • 17
  • 2
    Greenplum is based on Postgres 8.2 which did no have recursive queries. So I guess you are out of luck. Back in the days I think there was a additional module called "connectby" (or something similar) which would provide a function that retrieved the information recursively. –  Mar 23 '15 at 10:37
  • Thanks, Will it work if i upgrade the version of postgres, currently in my environment it is 8.2.15 – Gurupreet Singh Bhatia Mar 23 '15 at 11:39
  • 1
    As I said: recursive queries where introduced in Postgres with V8.4 so yes if you switch from Greenplum to a current Postgres version you will get the ability to run recursive queries –  Mar 23 '15 at 13:08

0 Answers0