(Error 1366 SQL) Incorrect DECIMAL value '()'

Question

I am developing a SP for a WindRose and when I try to run the SP, the SQL shows me this error.

(Error 1366 SQL) Incorrect DECIMAL value '()' for column "" at row -1

This problem appears after modifying the table where I collect the data. Before I made this SP for another table and it worked fine, but I had to change the table because this is the one receiving the live data.

CREATE TEMPORARY TABLE viento_count(angulo DECIMAL, val1 INT, val2 INT, val3 INT, val4 INT, val5 INT, val6 INT);
    
    myloop: LOOP
    
    IF angle > 350 THEN
        LEAVE myloop;
    END IF;

    SET angle1 = angle;
    SET angle2 = angle + 15;
    SET angle_grf = angle + 15/2;
    
    
    SET valor1 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy <= 3 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    SET valor2 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy > 3 AND speedy <= 6 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    SET valor3 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy > 6 AND speedy <= 9 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    SET valor4 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy > 9 AND speedy <= 13 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    SET valor5 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy > 13 AND speedy <= 18 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    SET valor6 = (SELECT COUNT(*) FROM vw_wind_counter WHERE speedy > 18 AND (device_id = codigo_estacion) AND (data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day) AND direccion > angle1 AND direccion <= angle2);
    
    INSERT INTO viento_count SELECT angle_grf AS angulo, valor1 AS val1, valor2 AS val2, valor3 AS val3, valor4 AS val4, valor5 AS val5, valor6 AS val6;
    
    SET angle = angle + 15;
    
    END LOOP myloop;
    
    SELECT * FROM viento_count;
    DROP TEMPORARY TABLE viento_count;  

Currently, I get the data from a view (vw_wind_counter), where I CAST the speed and direction data to DECIMAL values. I've saw another threads about this problem, but many of them is about wrong data insertion.

Answer 1

You never appear to initialise your angle variable to 0, so it's undefined.

That said, you don't need a variable or a loop...

Just use a table with the different angles you're interested in, and then use conditional aggregation (COUNT() with CASE expressions inside).

INSERT INTO
  viento_count (angulo, val1, val2, val3, val4, val5, val6)
SELECT
  CAST(a.angle AS DECIMAL(4,1)) + 7.5,
  COUNT(CASE WHEN                   w.speedy <= 3  THEN 1 END),
  COUNT(CASE WHEN w.speedy > 3  AND w.speedy <= 6  THEN 1 END),
  COUNT(CASE WHEN w.speedy > 6  AND w.speedy <= 9  THEN 1 END),
  COUNT(CASE WHEN w.speedy > 9  AND w.speedy <= 13 THEN 1 END),
  COUNT(CASE WHEN w.speedy > 13 AND w.speedy <= 18 THEN 1 END),
  COUNT(CASE WHEN w.speedy > 18                    THEN 1 END)
FROM
  vw_wind_counter   AS w
CROSS JOIN
(
  SELECT
    (a*5+b)*15   AS angle
  FROM
    (select 0 a union all select 1 union all select 2 union all select 3 union all select 4) t1,
    (select 0 b union all select 1 union all select 2 union all select 3 union all select 4) t2
  WHERE
    (a*5+b)*15 < 360
)
  AS a
WHERE
      w.device_id = codigo_estacion
  AND w.data_time BETWEEN fecha_consulta_first_day AND fecha_consulta_last_day
  AND w.direccion >  a.angle
  AND w.direccion <= a.angle + 15

Note: The sub-query just creates a table of angles from 0 to 345 in increments of 15.

Also: Ranges are traditionally (>= and <) rather than (> and <=). For example, your current code includes direccion = 360 but excludes direccion = 0, which is unusual.