Troubleshooting
Problem
An expression computed by SAP Hana is expected to return a DECIMAL value which includes decimal places.
SAP Hana JDBC driver is returning metadata describing a DECIMAL datatype with no decimal places.
SAP Hana JDBC driver is returning metadata describing a DECIMAL datatype with no decimal places.
Symptom
SAP Hana is computing an expression, where the SAP Hana JDBC driver describes one or more columns as having no decimal places.
While the value is expected to include decimal places, they are not displayed by Cognos Analytics.
While the value is expected to include decimal places, they are not displayed by Cognos Analytics.
Cause
An expression, such as multiplication uses a DECIMAL with large precision, and either, a BIGINT or DECIMAL with large precision.
When Cognos Analytics asks the SAP Hana JDBC driver to describe the result set, it returns a scale of zero for the DECIMAL column.
For more details on how SAP Hana calculates the precision and scale, refer to the following SAP Hana documentation.
For more details on how SAP Hana calculates the precision and scale, refer to the following SAP Hana documentation.
Similar expressions performed in other database vendors will return DECIMAL data types with a non-zero scale.
Diagnosing The Problem
Review the data type, precision and scale of terms used in expression.
Does it include a DECIMAL with a precision which is >= 21 and a BIGINT?
Does it include DECIMAL types with precision >= 19?
Resolving The Problem
To avoid these cases
- use decimal columns with smaller precision
- type cast expressions to use a decimal data type with required scale
Examples
1. Cast column C1 as decimal with a precision of 18 instead of >= 19
select sum ( CAST ( 1.23 as decimal(18, 2)) * cast (10 as bigint) ) from ....
2. Add explicit casting around the multiplication to return a decimal with required precision and scale
select sum ( CAST ( CAST ( 1.23 as decimal(19, 2)) * cast (10 as bigint) as decimal( 38, 2 ) ) ) from ...
Document Location
Worldwide
[{"Type":"SW","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m50000000Cl6nAAC","label":"Installation and Configuration->Data Sources"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
10 April 2021
UID
ibm16441999