Wahsei Wahsei - 5 months ago 14
MySQL Question

mysql error 'NULL' at line 1

I got this error when tried to execute this:


#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'NULL' at line 1


Can't seems to find what is the problem. Appreciate if anyone can help

SET @sql = NULL;

SELECT
GROUP_CONCAT(
DISTINCT CONCAT (
"SUM(IF(DATE(FROM_UNIXTIME(machine_stop)) = '",
DATE(FROM_UNIXTIME(machine_stop)),"' ,
(machine_start-machine_stop)/3600, 0)) AS ",
DATE(FROM_UNIXTIME(machine_stop))
)
) INTO @sql
FROM
downtime_data
WHERE
DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY;

SET @sql = CONCAT("SELECT
failure_code, ", @sql, "
FROM
downtime_data
WHERE
p.machine='HH1' AND
DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY
failure_code,
DATE(FROM_UNIXTIME(machine_stop))"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer

If by any chance the @sql variable still holds NULL value after the first select statement then you are going to encounter an exception later on while executing the prepare statement.

Look at the following select statement using CONCAT

SET @sql := NULL; SELECT CONCAT('abc',@sql,'def');

The result is NULL. Although you might expect the result to be abcdef.

In order to get abcdef you need to do this

SET @sql := NULL; SELECT CONCAT('abc',IFNULL(@sql,''),'def');

You may try any of the following if it resolves the issue:

Either

1) SET @sql := '';

OR

2) If you want to keep this line SET @sql = NULL; then change the portion of the final query like this SET @sql = CONCAT("SELECT failure_code, ", IF(@sql IS NULL, '',CONCAT(',',@sql)),

Here's the final query:

SET @sql = CONCAT("SELECT
                     failure_code ", IF(@sql IS NULL, '',CONCAT(',',@sql)), " 
                   FROM
                     downtime_data 
                   WHERE
                     p.machine='HH1' AND
                     DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY 
                   GROUP BY
                     failure_code,
                     DATE(FROM_UNIXTIME(machine_stop))"
                 );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Comments