Joined: 11 Jan 2006 Posts: 62 Location: Lower Saxony (DE)
I need a hint using common table expressions in a recursive query.
The thing is we have a self referencing table with a pk column MY_ID, a fk column PARENT_ID. Additionally the table has – beside some other columns – one ore two columns with numeric content. Let’s call them VALUE_1 and VALUE_2:
Is there anybody who has an idea how I have to code the query to get this result?
As I mentioned I’ve managed to code a cte sql that provides the list of rows without the sums as shown above. But I was not successful with the group sums yet. Til today all my trys did produce SQL code -345 / SQL state ‘42836’.
Of cause I can code a udf using a temporal table and summing up by iterating from the lowest level up to the top group. But I am looking for a one statement solution to avoid such logics and temporal tables.
WITH cte2(tel , my_id , parent_id , list_id , orig_id ) as(
select 0 as tel , a.my_id , a.parent_id , cast(a.my_id as varchar(20)) as list_id , a.my_id from INPDATA a
select cte2.tel + 1 , b.my_id , b.parent_id , b.my_id || '.' || cte2.list_id , cte2.orig_id from INPDATA b , cte2
where b.My_id = cte2.parent_id
and cte2.tel < 9)
select A.list_id , D.SUM_VAL_1, d.SUM_VAL_2
from cte2 A
join (select c.my_id, sum(a.value_1) as SUM_VAL_1, sum(a.value_2) as SUM_VAL_2 from cte2 C join inpDATA A
on a.mY_id = c.orig_id group by c.mY_id) D
on D.MY_ID = A.ORIG_ID
where A.parent_id is null
order by a.list_id