Division by zero was attempted in webi report BO 3.0

[Former Member]

Hi,

I created a object in universe level to calculate % of Cancel and % of Validation, These two objects are stored in one Class and i dont have any issues while generate the reports.. But, I want to add Product Name in the same report which the object is taken from another Class and while generate the report i am getting the errro divide by zero was attempted. i dont get any error when i validate the below code.

Can anyone let me know what causes the error when adding the Product Name?

Code :

SELECT

  SUM(DB2ADMIN.PRO_DLY. CNL_AMT),

  SUM(DB2ADMIN.PRO_DLY.PRO_AMT)

,

  SUM(DB2ADMIN.PRO_DLY. VLD_AMT )/SUM(DB2ADMIN.PRO_DLY. PRO_AMT )*100,

  SUM(DB2ADMIN.PRO_DLY. CNL_AMT )/SUM(DB2ADMIN.PRO_DLY. PRO_AMT )*100,

  DB2ADMIN.PRTDS.PRODUCT_DESC

FROM

  DB2ADMIN.PRO_DLY,

  DB2ADMIN.PRTDS,

  DB2ADMIN.DTE

WHERE

  ( DB2ADMIN.PRTDS.PRODUCT_KEY=DB2ADMIN.PRO_DLY.PRODUCT_KEY  )

  AND  ( DB2ADMIN.PRO_DLY.DATE_KEY=DB2ADMIN.DTE.DATE_KEY  )

  AND 

  date(DB2ADMIN.DTE.CAL_DAY_DT)  BETWEEN  @prompt('Enter Cal Date(Start):','D','Time Period (All Games)\Cal Date',Mono,Free,Persistent,,User:1)  AND  @prompt('Enter Cal Date(End):','D','Time Period (All Games)\Cal Date',Mono,Free,Persistent,,User:0)

GROUP BY

  DB2ADMIN.PRTDS.PRODUCT_DESC

Exact error from webi report :

"""

A database error occured. The database error text is: [IBM][CLI Driver][DB2/LINUXX8664] SQL0801N  Division by zero was attempted.  SQLSTATE=22012

. (WIS 10901)"""

Thanks,

[Former Member]

Hi Raj,

Instead of doing division at query level, you can do the division at report level.

Suppose, there are two objects [A] and [B] and [B] is returning zero value. In this case, you can create formula like:

=If IsError([A]/[B]) Then "" Else ([A]/[B])

In this case, report will display blank instead of #DIV/0 and data when [B] is non-zero.

Hope it will help.

Regards,

Yuvraj

[Former Member]

SUM(DB2ADMIN.PRO_DLY. VLD_AMT )/SUM(DB2ADMIN.PRO_DLY. PRO_AMT )*100,  
SUM(DB2ADMIN.PRO_DLY. CNL_AMT )/SUM(DB2ADMIN.PRO_DLY. PRO_AMT )*100

Looks like you have zero value when you aggregate DB2ADMIN.PRO_DLY. PRO_AMT.

You can try something like this

CASE WHEN (SUM(DB2ADMIN.PRO_DLY. PRO_AMT) <> 0) Then 

     ( Sum(DB2ADMIN.PRO_DLY. VLD_AMT) )/( Sum(DB2ADMIN.PRO_DLY. PRO_AMT) )*100 END

[Former Member]

Hi,

Great, Thanks for the info..

I was using the below code in Universe Object

@Aggregate_Aware(CASE WHEN (SUM(DB2ADMIN.PRO_DLY. PRO_AMT) <> 0)

THEN SUM(DB2ADMIN.PRO_DLY. CNL_AMT )/SUM(DB2ADMIN.PRO_DLY. PRO_AMT )*100 END)

Which works fine when i generate a report in BO 3.0 with the object(without filter). BUT while generate the same report by APPLYING filter as date range, i am getting the same error "Division by zero was attempted""

Thanks...

[former_member225163]

Pls check if NULL values has any effect on this.. just a try..

Cheers,

Bala

[Former Member]

No, I already verified which has no impacts.

Thanks,

Raj