How to fix this error '100051 (22012): Division by zero' in my SQL query - Snowflake

Question

I am trying to use the polynomial equation for each segment in each channel to extract the coefficients from those segment and calculate the residuals. I got the error below when I tried to run the query on snowflake below. Could you please advise me how to fix this error? I need really your help to fix this error.

The error is

100051 (22012): Division by zero

WITH c1 AS 
(
    SELECT 
        event_ts AS time,        
        VIMS:CH1_TEMP1:mean::DOUBLE AS temperature1_channel1,
        VIMS:CH2_TEMP2:mean::DOUBLE AS temperature2_channel2,    
        FLOOR((ROW_NUMBER() OVER (ORDER BY event_ts) - 1) / 48) + 1 AS segment_id,
        SERIAL_NUMBER
    FROM 
        TABLE1
    WHERE 
        (serial_number = 'XXX00305' 
         AND event_ts >= '2018-03-07 02:30:00' 
         AND event_ts <= '2019-02-13 17:00:00')
), c2 AS 
(
    SELECT
        segment_id,
        temperature1_channel1 - AVG(temperature1_channel1) OVER (PARTITION BY segment_id) AS x1,
        temperature2_channel2 - AVG(temperature2_channel2) OVER (PARTITION BY segment_id) AS x2,
        temperature1_channel1, temperature2_channel2,
        EXTRACT(MINUTE FROM time) AS time_min,
        POWER(EXTRACT(Minute FROM time), 2)*60*60 AS time_sec_sq
    FROM 
        c1
), c3 AS 
(
    SELECT 
        segment_id,        
        AVG(temperature1_channel1) AS avg_temp1,
        AVG(temperature2_channel2) AS avg_temp2,
        AVG(x1) AS a1, AVG(x2) AS a2, 
        SUM(x1 * time_min) / SUM(time_sec_sq) AS b1,
        SUM(x2 * time_min) / SUM(time_sec_sq) AS b2,

        -- Add more b variables for additional channels
        AVG(temperature1_channel1) - (SUM(x1 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x1 * time_min) / SUM(time_sec_sq)) AS c1,
        AVG(temperature2_channel2) - (SUM(x2 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x2 * time_min) / SUM(time_sec_sq)) AS c2
    FROM 
        c2
    GROUP BY 
        segment_id
), c4 AS 
(
    SELECT
        c1.time, c1.segment_id, c1.SERIAL_NUMBER, c3.avg_temp1, c3.avg_temp2,
        -- Add more avg_temp variables for additional channels
        c3.a1, c3.a2,
        -- Add more a variables for additional channels
        c3.b1, c3.b2,
        -- Add more b variables for additional channels
        c3.c1, c3.c2, 
        -- Add more c variables for additional channels
        c2.time_min
    FROM 
        c1
    CROSS JOIN 
        c3
    JOIN 
        c2 ON c1.segment_id = c2.segment_id
    GROUP BY 
        c1.time, c1.SERIAL_NUMBER, c1.segment_id, 
        c3.avg_temp1, c3.avg_temp2, c3.a1, c3.a2, c3.b1, c3.b2, 
        c3.c1, c3.c2, c2.time_min
)
SELECT
    c4.time,
    c4.segment_id,
    c4.SERIAL_NUMBER,
    c4.avg_temp1 AS Avg_temperature1,
    c4.avg_temp2 AS Avg_temperature2,
    c4.avg_temp1 - (c4.a1 * (c4.time_min * c4.time_min) + c4.b1 * c4.time_min + c4.c1) AS residual_temperature_1,
    c4.avg_temp2 - (c4.a2 * (c4.time_min * c4.time_min) + c4.b2 * c4.time_min + c4.c2) AS residual_temperature_2            
FROM 
    c4
WHERE 
    residual_temperature_1 IS NOT NULL
    OR residual_temperature_2 IS NOT NULL

Answer 1

As the error message indicates, the divisor in one of the divisions in your query is zero.

You could try using one of Snowflake's zero-safe division functions such as DIV0(), which returns 0 if the divisor is 0.

Looking at your query, my prime suspect would be the sum over the time_sec_sq column in the c3 query (CTE). Rewriting it like this should give you what you expected:

c3 AS 
(
    SELECT 
        segment_id,        
        AVG(temperature1_channel1) AS avg_temp1,
        AVG(temperature2_channel2) AS avg_temp2,
        AVG(x1) AS a1, AVG(x2) AS a2, 
        DIV0(SUM(x1 * time_min), SUM(time_sec_sq)) AS b1,
        DIV0(SUM(x2 * time_min), SUM(time_sec_sq)) AS b2,

        -- Add more b variables for additional channels
        AVG(temperature1_channel1) - DIV0(SUM(x1 * time_sec_sq), SUM(time_sec_sq)) - DIV0((SUM(x1 * time_min), SUM(time_sec_sq))) AS c1,
        AVG(temperature2_channel2) - DIV0(SUM(x2 * time_sec_sq), SUM(time_sec_sq)) - DIV0((SUM(x2 * time_min), SUM(time_sec_sq))) AS c2
    FROM 
        c2
    GROUP BY 
        segment_id
)

Snowflake documentation:

• https://docs.snowflake.com/en/sql-reference/functions/div0
• https://docs.snowflake.com/en/sql-reference/functions/div0null