Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Summing up child-values in self-referencing table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 40
Location: Lower Saxony (DE)

PostPosted: Sun Feb 14, 2016 6:02 pm    Post subject: Summing up child-values in self-referencing table
Reply with quote

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
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Feb 17, 2016 9:52 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us