Data Provider Query: Decimal precision not honored in PostgreSQL

Products

• Clarity FedRAMP
• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

In Clarity, the "Number Display" View Option honors columns where the data type is Numeric and the extended data type is Double. Clarity derives this extended data type based on the scale defined at the database level; if the scale is greater than 0, it is evaluated as a Double. 

When using the COALESCE function within SQL columns in a PostgreSQL Data Provider Query (e.g., COALESCE(numeric_column, 0)), the database may return a scale of 0. Consequently, Clarity evaluates the column as a standard Number rather than a Double, causing the Number display options (decimal precision) to fail 

Environment

Clarity 16.4.1,16.4.2 and higher 

Resolution

• To recognize the column as a Double and ensure the Number Display View Option functions correctly, customers must explicitly typecast the COALESCE expression. 
  
  
• Clarity supports a maximum scale of 6 for these types. You can use any of the following methods to typecast your expression: 
  
  • ::float
  • ::float8
  • ::numeric(32, 6)
    
    
• Example of Corrected Query 
  
  • The following query causes the issue because the database returns a scale of 0:
    

    • COALESCE(coalesce(inv.r_tot_cst_i, 0) / nullif(inv.r_beac_tot, 0), 0) pct_spent_ttl

  • Corrected Query (Forces Scale > 0), To resolve the issue, replace the expression with one of the following typecasted formats:

    -- Using ::float
    COALESCE (coalesce (inv.r_tot_cst_i, 0) / nullif (inv.r_beac_tot, 0), 0)::float pct_spent_ttl
    -- Using ::float8
    COALESCE (coalesce (inv.r_tot_cst_i, 0) / nullif (inv.r_beac_tot, 0), 0)::float8 pct_spent_ttl
    -- Using ::numeric(32, 6)
    COALESCE (coalesce (inv.r_tot_cst_i, 0) / nullif (inv.r_beac_tot, 0), 0)::numeric(32,6) pct_spent_ttl
    

    
    
    

 

Additional Information

Workaround: Data Provider Query for numeric values to honor the decimal precision in View Options. Select appropriate format in the view option

• This issue is specific to Data Provider Queries utilizing PostgreSQL.
• The system supports a maximum scale of 6 when using the ::numeric typecast method.