Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hi there,
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:
| Code: |
MY_ID PARENT_ID VALUE_1 VALUE_2
01 {} {} 01
02 {} {} 11
03 01 {} 02
04 01 {} 03
05 02 {} 22
06 02 {} 33
07 03 04 04
08 03 05 05
09 04 06 06
10 04 07 07
11 05 44 44
12 05 55 55
13 06 66 66
14 06 77 77 |
I know to query these data ‘serialized’ i.e. ordered by the path of the IDs with a cte (concatenating the IDs to a new varchar column ID_PATH) so the result is:
| Code: |
ID_PATH MY_ID PARENT_ID
01 01 {}
01.03 03 01
01.03.07 07 03
01.03.08 08 03
01.04 04 01
01.04.09 09 04
01.04.10 10 04
02 02 {}
02.05 05 02
02.05.11 11 05
02.05.12 12 05
02.06 06 02
02.06.13 13 06
02.06.14 14 06 |
But the goal is to sum up the values by the parents so the results should be
| Code: |
ID_PATH VALUE_1 SUM_1 VALUE_2 SUM_2
01 {} 022 /* = 09 + 13 */ 01 028 /* = 1 + 11 + 16 */
01.03 {} 009 /* = 04 + 05 */ 02 011 /* = 2 + 4 + 5 */
01.03.07 04 004 04 004
01.03.08 05 005 05 005
01.04 {} 013 /* = 06 + 07 */ 03 016 /* = 3 + 6 + 7 */
01.04.09 06 006 06 006
01.04.10 07 007 07 007
02 {} 242 /* = 99 + 143 */ 11 308 /* = 11 + 121 + 176 */
02.05 {} 099 /* = 44 + 55 */ 22 121 /* = 22 + 44 + 55 */
02.05.11 44 044 44 044
02.05.12 55 055 55 055
02.06 {} 143 /* = 66 + 77 */ 33 176 /* = 33 + 66 + 77 */
02.06.13 66 066 66 066
02.06.14 77 077 77 077 |
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.
Thanks a lot for your helpful hints |
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
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
union all
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
|
|
|
