2019 Sep 19 9:38 AM
Hi,
I am trying to add two fields of one CDS view in other CDS view. I am getting Zero as the output if any of the operand is Zero.
In the first CDS I am getting the SUM of sum field as
@DefaultAggregation: #SUM
sum( total.tmp_count ) as total,
@DefaultAggregation: #SUM
sum( blank.tmp_count ) as blank,
@DefaultAggregation: #SUM
sum( error.tmp_count ) as error
and in the second CDS I am adding the fields Total and error as
total,
error,
total + error as Total_error
and the output is
so if any of the operand (either error/total) is zero my output (Total_error) is coming as Zero.
I tried the below cases also
all these cases i am getting the same output.
Any input will be highly appreciated.
2019 Nov 14 11:28 AM
I suspect you are obtaining column "Error" from a left outer join? If so, when no record is found to fulfil the join the result is NULL. The calculation of ANY VALUE + NULL = ZERO. This is why the "Total error" column is zero in these cases.
Change the arithmetic operation from
total + error as Total_error
to
case when error is not null then total + error
else total end as Total_error
Hope that helps.
2019 Sep 19 10:17 AM
Hi,
Can you try using 'AS' for fields before addition? like:
total as Total1,
error as Error1,
Total1 + Error1 as TotalError1.
Regards
GK
2019 Sep 19 11:03 AM
Thanks for your reply.
Total1 + Error1 as TotalError1 is not accepting as these fields are not present in the CDS we are referring to.
I tried
total as Total1,
error as Error1,
total + error as TotalError1.
Even that is giving the same result as before.
2019 Sep 19 12:34 PM
2019 Sep 23 8:00 PM
2019 Sep 24 11:22 AM
2019 Sep 24 2:07 PM
probably you have to post whole code for us to get more idea.....
2019 Nov 14 11:28 AM
I suspect you are obtaining column "Error" from a left outer join? If so, when no record is found to fulfil the join the result is NULL. The calculation of ANY VALUE + NULL = ZERO. This is why the "Total error" column is zero in these cases.
Change the arithmetic operation from
total + error as Total_error
to
case when error is not null then total + error
else total end as Total_error
Hope that helps.
2020 Apr 21 12:50 PM
This kind of works. Is there any alternate solution?
In the above example he is trying to add 2 fields. But for my scenario I need to add 4 fields.
With this approach, I need to create nested cases which is kind of confusing to understand.
2020 Jan 03 5:21 AM
Dear Paul,
please try the following,
(cast( case when total is not null then total else 0 end as abap.dec( 25,2 ) ) +
cast( case when error is not null then error else 0 end as abap.dec( 25,2 ) ) ) as total_error.
regards,
Raghav
2020 Apr 21 12:30 PM
Even I am facing the same problem now. Is there a solution for this ?
2020 May 06 10:09 AM
The solution is simple but I think this issue is a bug of CDS views. If you have 2 only 2 fields null control is working properly but sometimes arithmetic operations have a more complex scenario. The solution is simple you have to create a new CDS view.
For example:
CDS view 1
@DefaultAggregation: #SUM
sum( total.tmp_count ) as total1,
@DefaultAggregation: #SUM
sum( blank.tmp_count ) as total2,
@DefaultAggregation: #SUM
sum( error.tmp_count ) as total3,
@DefaultAggregation: #SUM
sum( error.tmp_count ) as total4,
CDS view 2 ( New one )
case when total1 is null then 0 else total1 end as total1 ,
case when total2 is null then 0 else total2 end as total2 ,
case when total3 is null then 0 else total3 end as total3 ,
case when total4 is null then 0 else total4 end as total4 ,
CDS view 3
total1 + total2 - ( total3 + total4 ) as result,
Regards
OAP