I have following tables
(repeated ~n times with same nr_key2 and different value)
in table2 nr_key1 = nr_key2
After I have iterated through nr_key1 I want to use recursion to select nr_key1 = nr_dad1 and iterate again table2 with the new nr_key1. This should be repeated as long as nr_dad1 is null. I have succesfully got the result but only if nr_key1 = nr_key2 contains values. If table2 is empty for the current nr_key1 my recursion is broken. I receive error if I try to use outer join in my recursion. Also my current solution is quite slow, because I am iterating the whole hierarchy again from each value found in table2. Is it possible to implement some sort of a check?
Maybe my approach is wrong? Any suggestions will be highly appreciated thanks.
with test (NR_KEY1, NR_DAD1, VALUE, leveli) AS ( select a.NR_KEY1, a.NR_DAD1, VALUE, 1 as leveli from table1 right outer join table2 a ON NR_KEY2 = a.NR_KEY1 where a.NR_KEY1 = 10020 union all select a.NR_KEY1, a.NR_DAD1, e.VALUE, eh.leveli + 1 as leveli from table1 e inner join table2 a ON e.NR_KEY2 = a.NR_KEY1 inner join test eh ON e.NR_KEY2 = eh.NR_DAD1 where e.NR_KEY2 = eh.NR_DAD1 ) select * from test order by leveli