display sum of lower level values to higher level in hana data

[rajark]

Hi all this is the query i am using

SELECT

m.solversionid,

m.variant,

m.description,

m.level,

m.services_crmid,

m.parentnode,

m.itemtype,

CASE

WHEN m.level = 1 THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode AND sub.level = 2

), 0)

WHEN m.level = 0 AND m.description != 'Total' THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode AND sub.level = 1

), 0)

WHEN m.level = 0 AND m.description = 'Total' THEN

COALESCE((

SELECT SUM(sub.sspmaxmargin)

FROM CV_FIN_FINAL_MIGRATION sub

WHERE sub.parentnode = m.parentnode

), 0)

ELSE

m.sspmaxmargin

END AS sspmaxmargin

FROM CV_FIN_FINAL_MIGRATION m

WHERE m.solversionid = '2371791525-055' AND m.variant = '0002'

ORDER BY m.level;

and the result i am getting is below i am seeing all 0 values for level 1 and 0 i should get sum of level 2 in level 1 and sum of level 1 in level 0

[fedaros · Accepted solution]

Hi rajark,

Is understood what you want but the way you are doing just doesn't work this way.

Imagine that first you are selecting all rows that attend where below:

WHERE m.solversionid = '2371791525-055' AND m.variant = '0002'

And you create new columns that go again on same calculation view with missing filter on solverid and variant and also filtering parentnode which will only found values for the last level which has values and has same parent.

WHERE sub.parentnode = m.parentnode AND sub.level = 2

WHERE sub.parentnode = m.parentnode AND sub.level = 1

WHERE sub.parentnode = m.parentnode

You should link the current row m.services_crmid with the childs sub.parent_node, and also considering solveid and variant on filters of sub-selections.

Another miss consideration from your side is that SQL ANSI came from sets of data, but you don't have the values (sums) already made for all levels, so you can do this arrangement to sum level 1 from level 2, but when you try to sum level 0 from level 1 it will not have any value. It will only work for level 0 if you manual code to sum level 1 from result of sum from level 2.

That said, if you have a fixed number of levels you can model if fixed within SQL but if not you need a navigation function like HIERARCHY_DESCENDANTS_AGGREGATE:

HANA Hierarchy Developer Guide

Regards, Fernando Da Rós

[fedaros · Accepted solution]

The example below simulate a navigation hierarchy in a fixed set of 3 levels and all values exclusively on level 2.

Each inner select provide the values for the level on WHERE, and parent selections must start reading values from previous level.

WITH
  lt_level2 as (select 
    a.solversionid,a.variant,a.description,a.services_crmid,a.parentnode,a.itemtype,SUM(a.sspmaxmargin) as sum_sspmm
	FROM CV_FIN_FINAL_MIGRATION as a
	WHERE a.level = 2),
  lt_level1 as (select 
    a.solversionid,a.variant,a.description,a.services_crmid,a.parentnode,a.itemtype,
	SUM(b.sum_sspmm) as sum_sspmm
	FROM CV_FIN_FINAL_MIGRATION as a left outer join lt_level2 as b
	on b.parentnode = a.services_crmid
	WHERE a.level = 1),
  lt_level0 as (select 
    a.solversionid,a.variant,a.description,a.services_crmid,a.parentnode,a.itemtype,
	SUM(b.sum_sspmm) as sum_sspmm
	FROM CV_FIN_FINAL_MIGRATION as a left outer join lt_level1 as b
	on b.parentnode = a.services_crmid
	WHERE a.level = 0),
  select * from (
	select * from lt_level0 union
	select * from lt_level1 union
	select * from lt_level2
	)
  where solversionid = '2371791525-055' AND variant = '0002';

Regards, Fernando Da Rós

[rajark · Accepted solution]

@Fernando Da Rós Thanks for your explanation and query, this solved my issue