IBM Support

Unable to cast a Float field as an Integer: RQP-DEF-0177, UDA-SQL-0564, UDA-CUR-0000

Troubleshooting


Problem

In Framework Manager, when trying to cast a Float field containing large values into an Integer data type, an Arithmetic Overflow error message occurs.

Symptom

RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Arithmetic overflow error for type int, value = 2210000101.000000. (SQLSTATE=22003,
SQLERRORCODE=232)
UDA-CUR-0000 Unable to fetch the row.

Cause

This is caused by values larger than 2147483647 in the float field. A signed, 4-byte integer datatype only allows for values up to positive (2^31) - 1.

Environment

Reporting Database: Microsoft SQL Server

Resolving The Problem

You can cast the field as a bigint datatype instead. The expression editor for a field in a Model Query Subject will not allow you to use bigint as a datatype, so you must make this change in the SQL of the underlying Data Source Query Subject instead.

Steps:1. Locate the underlying Data Source Query Subject in your FM model which is bringing back the float field in question.

2. Right-click it, and choose Properties.

3. On the SQL tab, edit the SQL so that instead of selecting floatField, you are selecting cast(floatField AS bigint) as "intField"

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Framework Manager","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21340969