How to avoid data filter issues in real-time object due to numeric precision limit?

Posted over 3 years ago by Smarten - Solution Team

Post a topic
Answered
Smarten - Solution Team
Smarten - Solution Team Admin

Issue / Query: 

The tabular is showing ‘Unable to apply page filter’ error when ‘some’ values are selected from the page filter kept for Location column. The tabular is created from a real-time cube (using PL/SQL query) and the data type of the location is ‘Varchar’. The data is fetched from Oracle datasource. It throws ‘Precision 77 exceeds 38’ error in the application logs.


0 Votes

Smarten - Solution Team

Smarten - Solution Team posted over 3 years ago Admin Best Answer

Reason:

This type of error is a typical oracle query related error that happens due to the numeric overflow of values. If any of the query-returned numeric value exceeds the maximum supported precision, then this type of error will be thrown. The numeric columns are not necessarily from the original tables. They could be calculated values for the output in the PL/SQL queries.

For example, there can be numeric column values like “-8.5656545544854889123015889455454510” that exceeds the supported precision.

In real-time objects (tabular, cross-tab, graph, smartenview, kpi, etc.) when you apply filter in the object (that is of String / Varchar data type), and if the result has values as per the above behavior, a message will be shown as ‘Unable to apply filter’ along with the error captured in the logs.


Solution:

This can be handled in the source of the data or PL/SQL query or any other routine that is passing data to Smarten Data source, by rounding off or truncating the values. Use round (number, decimal places) function or trunc (number, decimal places) to truncate the values in the result. The result for the above example if you are rounding off to 2 decimal places will be “8.57”. If you’re truncating the values, then the result will be “8.56”.

0 Votes


1 Comments

Smarten - Solution Team

Smarten - Solution Team posted over 3 years ago Admin Answer

Reason:

This type of error is a typical oracle query related error that happens due to the numeric overflow of values. If any of the query-returned numeric value exceeds the maximum supported precision, then this type of error will be thrown. The numeric columns are not necessarily from the original tables. They could be calculated values for the output in the PL/SQL queries.

For example, there can be numeric column values like “-8.5656545544854889123015889455454510” that exceeds the supported precision.

In real-time objects (tabular, cross-tab, graph, smartenview, kpi, etc.) when you apply filter in the object (that is of String / Varchar data type), and if the result has values as per the above behavior, a message will be shown as ‘Unable to apply filter’ along with the error captured in the logs.


Solution:

This can be handled in the source of the data or PL/SQL query or any other routine that is passing data to Smarten Data source, by rounding off or truncating the values. Use round (number, decimal places) function or trunc (number, decimal places) to truncate the values in the result. The result for the above example if you are rounding off to 2 decimal places will be “8.57”. If you’re truncating the values, then the result will be “8.56”.

0 Votes

Login to post a comment