Skip to Content
author's profile photo Former Member
Former Member

Division by zero was attempted in webi report BO 3.0


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,

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 26, 2013 at 05:16 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 04, 2014 at 02:52 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.