I know that Postgres doesn't support multiple recursive reference in same query. So I tried to re-write the query to get the same result but so far didn't have any luck.
can someone please help me how I can achieve this?
Postgres Query:-(works fine in Db2 without 'recursive')
with recursive parent(level,pid,name,depth)
as ( select t1.level,t1.pid,t1.name,0 from table1 t1, table2 t2,table3 t3 where t1.pid=t2.pid and t2.name=t3.name and t3.owner='me'
union all
select t11.level,t11.pid,t11.name,p.depth-1 from table1 t11, parent p where t11.pid = p.lev and p.depth<=0
union all
select t12.level,t12.pid,t12.name,p.depth+1 from table1 t12, parent p where t12.pid = p.pid and p.depth>=0)
select * from parent fetch first 5 rows only
**Error: recursive reference to query "parent" must not appear within its non-recursive term**
Table1
- - - - - - - - - - -
level | pid | name
.....................
11 | 1 | X1
3 | 2 | X2
1 | 4 | X4
6 | 11 | X11
4 | 12 | X12
Table2
- - - - - - - - - - -
pid | server | name
..................... ...
12 | s1 | X12
4 | s11 | X4
1 | s1 | X1
2 | s | X2
Table3
- - - - - - -
owner | name
..............
me | X1
1 | X4
me | X2
Expected Result
LEV | ID | NAME | DEPTH
..................... ...
1 | 4 | X4 | 0
11 | 1 | X1 | -1
6 | 11 | X11 | -2
1 | 4 | X4 | 2
1 | 4 | X4 | 3
try this
with recursive parent(level,pid,name,depth)
as (
with recursive innerparent(level,pid,name,depth)
as ( select t1.level,t1.pid,t1.name,0 from table1 t1, table2 t2,table3 t3
where t1.pid=t2.pid and t2.name=t3.name and t3.owner='me'
union all
select t11.level,t11.pid,t11.name,p.depth-1 from table1 t11,
innerparent p where t11.pid = p.lev and p.depth<=0 ) select * from
innerparent
union all
select t12.level,t12.pid,t12.name,p.depth+1 from table1 t12, parent p
where t12.pid = p.pid and p.depth>=0)
select * from parent fetch first 5 rows only