Issue with arithmetic operation in CDS views.

[former_member547606]

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

1. casting the SUM values in the first CDS to integer.
2. Casting error and total to integer before adding.
3. Put a case statement to error to check whether it is Zero, in case of zero it should display only total else display total + error.
4. In the case statement it is not accepting NULL or '0'.

all these cases i am getting the same output.

Any input will be highly appreciated.

[GK817]

Hi,

Can you try using 'AS' for fields before addition? like:

total as Total1,

error as Error1,

Total1 + Error1 as TotalError1.

Regards

GK

[former_member547606]

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.

[former_member1716]

Can you please let us know your select Query?

[junwu]

what is the result when you preview your first cds ?

[former_member547606]

I am getting the below as the output for my first CDS

[junwu]

probably you have to post whole code for us to get more idea.....

[former_member185932]

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.

[former_member671874]

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.

[raga6600]

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

[former_member671874]

Even I am facing the same problem now. Is there a solution for this ?

[former_member333072]

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