Options

Vertica 8.0 error Division by zero when trying to sum values

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

Comments

  • Options

    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?

  • Options

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file