Query not working after data update

[Former Member]

I have constructed a query that utilizes sub-queries to do a bit of calculation

insert into "schema1."schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries"

select 'RPAVG001' as "MODEL_ID", c."PEER_GROUP_ID", c."PRODUCT_ID", c."KEY_FIGURE", c."VALID_FOR_DATE", c."VALID_FOR_MONTH", c."VALUE"-(g."VALUE" *

( (select avg("VALUE") from "schema1."schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries" c1 where c1."PRODUCT_ID"=c."PRODUCT_ID" )

/ (select avg("VALUE") from "TimeSeries.CostRevenueKeyFigureSeries" g1 where g1."SERIES_ID"=g."SERIES_ID" )) )

as "VALUE" from "schema1."schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries" c,"TimeSeries.CostRevenueKeyFigureSeries" g

where c."PEER_GROUP_ID"=g."SERIES_ID" and c."VALID_FOR_DATE"=g."VALID_FOR_DATE"

It used to work fine but recently some data has been updated in the schema and since then it has started throwing the following error:

Could not execute 'insert into "schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries" select 'RPAVG001' ...' in 1:36.270 minutes .

  SAP DBTech JDBC: [2048]: column store error: search table error:  [6859] AttributeEngine: divide by zero;double comma(string 'RPAVG001', double sqlsub(double "schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries", double times(double "TimeSeries.CostRevenueKeyFigureSeries.VALUE", double [here]divide(double double(decfloat "MySchema.#_SYS_QO_COL_7f7000175450:400000002d86f8a.COL$0$"), double double(decfloat "MySchema.#_SYS_QO_COL_7f7000186860:400000002d86f93.COL$0$"))))),(TimeSeries.CostRevenueKeyFigureSeries.VALUE = 0[double], schema1::TimeSeries.CostRevenueSubscriberKeyFigureSeries.VALUE = 0[double], MySchema.#_SYS_QO_COL_7f7000175450:400000002d86f8a.COL$0$ = 0[decfloat], MySchema.#_SYS_QO_COL_7f7000186860:400000002d86f93.COL$0$ = 0[decfloat]); checkNull false

As I'm relatively new to database programming and HANA, I would really appreciate if someone more knowledgeable then myself would elaborate what the error means and how can I resolve it.

[pfefferf]

Hello Muhammad,

as it can be seen from the error message you are getting a "divison by zero" error.

The updated data will cause that the result of the average calculation you do and which result is used as right hand side argument in the division calculation is zero.

If you wanna use zero in such situations you can use the CASE Expression following that pseudo coding

...

CASE

WHEN average value = 0 THEN 0

ELSE do calculation

END

...

Regards,

Florian