Vertica 8.0 error Division by zero when trying to sum values

max_jortilles

Hi all!

I am trying the following query where I am doing the following:

    SELECT
        sum(recibo_prima * round( DATEDIFF( 'day', TO_DATE( '2016-01-02', 'YYYY-MM-DD' ), recibo_fechaefecto )/ DATEDIFF( 'day', recibo_fechavto, recibo_fechaefecto )/ DATEDIFF( 'day', recibo_fechavto, recibo_fechaefecto ), 6 )::FLOAT )as prima_periodificada
    FROM
         table

This query results in a series of numeric values as can be seen in the first screenshot.

The problem occurs when I try to sum said values, I get this error

SQL Error [3117] [22012]: [Vertica][VJDBC](3117) ERROR: Division by zero
  [Vertica][VJDBC](3117) ERROR: Division by zero
    com.vertica.util.ServerException: [Vertica][VJDBC](3117) ERROR: Division by zero

Could you please tell me what I am doing wrong?

Thanks in advance

PS: I tried tweaking AllowNumericOverflow ** & **NumericSumExtraPrecisionDigits but didn't help

peterjansens

I see you are dividing by DATEDIFF; the difference between two dates (twice).
What should happen is the two dates are the same, making the difference zero?

anshrestha

Please provide output for following:
select get_config_parameter('DivideZeroByZeroThrowsError');