Avoid data filter issues due to numeric precision limit

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

Issue / Query: 

The tabular is showing ‘Unable to apply page filter’ error when ‘some’ values are selected from the page filter kept for the Location column. The tabular is created from a real-time Dataset (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      

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 exceed 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”.