510 views

Resolving Precision Errors in Web Service SUM Functions

 

Problem
A SQL SUM function returns an unexpected negative result.

 

Symptoms
When performing a SUM function in a query that should always return a positive value, such as a query to determine the amount of time elapsed between an incident being opened and resolved, the query returns a negative value.

  

Cause
The SUM function expects operands with a different quantity of digits than was provided, leading to an incorrect result.

 


Resolution
Cast any number passed to the SUM function to a Numeric with an explicitly-defined maximum length.

For example, if the SQL statement returning an invalid value is:

SELECT assignment_group, dv_assignment_group, SUM(TIMESTAMPDIFF(SECOND, opened_at, resolved_at)) FROM incident;

 

Change this statement to:

SELECT assignment_group, dv_assignment_group, SUM(CAST(TIMESTAMPDIFF(SECOND, opened_at, resolved_at) as NUMERIC(38,0))) FROM incident;

 

Article Information

Last Updated:2016-03-10 12:36:11
Published:2014-10-23