Arithmetic error for only one value rest values works fine

[tarunkrdas2013]

I have a Relational report where in whenever i enter particlar number as 1000 as Input value report throws below arithmetic error.I have pasted the Native SQL
if that helps in finding the issue as attachment with this thread.Please note this issue occurs only for number as 1000 and this number has business significance
so cant be ignored



RQP-DEF-0177


An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

UDA-SQL-0144 An arithmetic exception was detected.[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
UDA-CUR-0000 Unable to fetch the row.RSV-SRV-0042 Trace back:RSReportService.cpp(724): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(859): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(314): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(904): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(600): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(340): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(185): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardRSResultSetIterator.cpp(1504): QFException: CCL_RETHROW: RSResultSetIterator::retrieveDataQFSPartialDataset.cpp(80): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIteratorSource/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE

[cognostechie]

I have a Relational report where in whenever i enter particlar number as 1000 as Input value report throws below arithmetic error.I have pasted the Native SQL
if that helps in finding the issue as attachment with this thread.Please note this issue occurs only for number as 1000 and this number has business significance
so cant be ignored



RQP-DEF-0177


An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

UDA-SQL-0144 An arithmetic exception was detected.[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
[IBM][CLI Driver][AS] SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=01519
UDA-CUR-0000 Unable to fetch the row.RSV-SRV-0042 Trace back:RSReportService.cpp(724): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(859): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(314): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(904): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(600): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(340): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(185): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardRSResultSetIterator.cpp(1504): QFException: CCL_RETHROW: RSResultSetIterator::retrieveDataQFSPartialDataset.cpp(80): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIteratorSource/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE

What do you mean by entering 1000 as value? Are you talking about a report or data entry screen? Is there a prompt in the report where you enter 1000? or is the report getting 1000 as data from the backend? This is a technical forum and people are not supposed to read your mind so be precise or don't expect an answer.

[BigChris]

I can't see any prompts in the code that you posted, so I'm assuming you're putting test data into a table and running the report over that table. Can you let us know what field you're putting 1000 into please? Your code is rather convoluted with something like 6 sub-queries, but this looks like the main bit that's doing the work:

Code Select Expand

where "BI_DATE_DIM"."FSCL_YR_NUM" = cast('2015' as varchar( 10 ))
and "BI_DATE_DIM"."FSCL_PD_NUM" = cast('13' as varchar( 10 )) and 1 = 1
and "coguda20"."COMPANY_NUM" in (1, 4)
and "BI_DEPARTMENT_DIM_VW"."MAJOR_DEPT_GRP_NUM" >= 0
and "BI_DEPARTMENT_DIM_VW"."MEGA_DEPT_GRP_NUM" > 0
and "coguda20"."BI_DIVISION_CODE"  NOT  in ('INTERNATIONAL')
and "coguda20"."COMPANY_CODE" in (N'US', N'CN')
and cast("coguda20"."LOCATION_OPEN_DATE" as date) <> DATE '1900-01-01'
and "coguda20"."LOCATION_OPEN_DATE" <= cast(((current_date) + ((-1) * INTERVAL '1 DAY')) as DATE)
and "coguda20"."LOCATION_SUB_TYPE_NUM" in (12, 14, 23, 27)
and "coguda20"."LOCATION_CLOSE_DATE" = DATE '9999-12-31'
and "BI_DATE_DIM"."CALENDAR_DATE" <= cast(((current_date) + ((-1) * INTERVAL '1 DAY')) as DATE)
and "BI_DATE_DIM"."RELATIVE_FSCL_YR" >= -3
and "T4"."DATE_ID" = "BI_DATE_DIM"."DATE_ID"
and "T4"."LOCATION_ID" = "BI_LOCATION_DIM_VW"."LOCATION_ID"
and "BI_LOCATION_DIM_VW"."REPORTING_LOCATION_NUM" = "coguda20"."LOCATION_NUM"
and "T4"."ACTIVITY_DEPARTMENT_ID" = "BI_DEPARTMENT_DIM_VW"."DEPARTMENT_ID"
and "coguda20"."LOCATION_NUM" = "coguda21"."LOCATION_NUM"
and "coguda20"."COMPANY_NUM" = 1
and "coguda21"."REPORTING_LOCATION_NUM" = "coguda20"."LOCATION_NUM"

There are various aggregations in the code, but I didn't notice any divisions (so you're not dividing by very small decimals to produce very large numbers).

Give us a bit more info and we can try to help, but at the moment I can't see where the problem is.

[tarunkrdas2013]

Hi ,

Yes this report has a prompt which asks for a number so when we put number as 1000 this arithmetic error comes out

[tarunkrdas2013]

Posted full report query

[BigChris]

Thanks for posting the SQL, but what are you being prompted for?

[tarunkrdas2013]

warehouse number 1000

[Lynn]

It looks like your warehouse number is being cast as decimal(5,0) which doesn't really make any sense. Not sure if that is the cause of your error but I'd suggest changing that aspect to see if you can isolate the issue.

[tarunkrdas2013]

Thank you Lynn...