找到你要的答案

Q:prepared query shows error

Q:准备查询显示错误

I am trying to make a prepared statement. I want to generate dynamic column for 'Cabang'. This is my prepared statement:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(

    'coalesce((max(CASE WHEN Cabang.ID_Cabang = ''', 
    Cabang.ID_Cabang, ''' THEN PWT END)),0) AS `', 
    Cabang.cabang,'`'
    )
) INTO @sql
FROM keyperformanceindicator
INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang;


SET @sql =  CONCAT(
                 SELECT @sql,

                 FROM `keyperformanceindicator`

        INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
        INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang
        INNER JOIN 
        (

            SELECT 
            DATE_FORMAT( PilotStationDate, '%m' ) AS MONTH , 
            FORMAT( SUM(`PilotWaitingTime`) / COUNT('PilotStationDate'), 3 ) AS PWT, 
            COUNT( 'PilotStationDate' ) AS jumlah

            FROM keyperformanceindicator
            INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
            GROUP BY DATE_FORMAT( PilotStationDate, '%m' )

        )x ON DATE_FORMAT( PilotStationDate, '%m' ) = x.Month

        GROUP BY DATE_FORMAT(PilotStationDate, '%m')
        ORDER BY  UNIX_TIMESTAMP( CONCAT_WS( '', PilotStationDate, ' ', PilotStationTime ) ) ASC
        );

PREPARE stmt FROM @sql;
EXECUTE stmt;

-- Clear something
DEALLOCATE PREPARE stmt;
SET @sql = NULL;

it shows error like this :

ERROR 1064 (42000): 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 'SELECT @sql, FROM `keyperformanceindicator` INNER JOIN pilot ON ' at line 2

PREPARE stmt FROM @sql;
ERROR 1064 (42000): 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 'coalesce((max(CASE WHEN Cabang.ID_Cabang = 'C001' THEN PWT END)),0) AS `Belawan`,max(CASE WHEN' at line 1

Here's a fiddle

I am new in using prepared statement. Thank you for your help.

我试图做一个准备的声明。我想为“一道”生成动态柱。这是我准备好的声明:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(

    'coalesce((max(CASE WHEN Cabang.ID_Cabang = ''', 
    Cabang.ID_Cabang, ''' THEN PWT END)),0) AS `', 
    Cabang.cabang,'`'
    )
) INTO @sql
FROM keyperformanceindicator
INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang;


SET @sql =  CONCAT(
                 SELECT @sql,

                 FROM `keyperformanceindicator`

        INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
        INNER JOIN cabang ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang
        INNER JOIN 
        (

            SELECT 
            DATE_FORMAT( PilotStationDate, '%m' ) AS MONTH , 
            FORMAT( SUM(`PilotWaitingTime`) / COUNT('PilotStationDate'), 3 ) AS PWT, 
            COUNT( 'PilotStationDate' ) AS jumlah

            FROM keyperformanceindicator
            INNER JOIN pilot ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot
            GROUP BY DATE_FORMAT( PilotStationDate, '%m' )

        )x ON DATE_FORMAT( PilotStationDate, '%m' ) = x.Month

        GROUP BY DATE_FORMAT(PilotStationDate, '%m')
        ORDER BY  UNIX_TIMESTAMP( CONCAT_WS( '', PilotStationDate, ' ', PilotStationTime ) ) ASC
        );

PREPARE stmt FROM @sql;
EXECUTE stmt;

-- Clear something
DEALLOCATE PREPARE stmt;
SET @sql = NULL;

它显示这样的错误:

ERROR 1064 (42000): 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 'SELECT @sql, FROM `keyperformanceindicator` INNER JOIN pilot ON ' at line 2

PREPARE stmt FROM @sql;
ERROR 1064 (42000): 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 'coalesce((max(CASE WHEN Cabang.ID_Cabang = 'C001' THEN PWT END)),0) AS `Belawan`,max(CASE WHEN' at line 1

这里有一把小提琴

我在使用新的声明。谢谢你的帮忙。

mysql  prepared-statement