How to execute this query: SELECT table_row FROM table AS concat(sth, sth)? Returning error at the c-CodePudding

I have this MySQL procedure:

CREATE PROCEDURE Employee_Dilligence(IN No_Hours INT)
BEGIN 
SELECT EmpName,employees.EmpID, Hours_Worked, 
        Dilligence(Hours_Worked/(TIMESTAMPDIFF(Day, StartDate, CURDATE())*No_Hours)) 
        AS concat('Worked more than', No_Hours, 'Hours')
    
FROM company.employees INNER JOIN company.employee_project INNER JOIN company.projects
ON employees.EmpID = employee_project.EmpID AND employee_project.ProjID = projects.ProjID
WHERE projects.ProjID = ProjectID;

And it works correctly, except for one thing: the concat here:

AS concat('Worked more than', No_Hours, 'Hours')

I do not understand why, though. The error message I'm getting is:

Error Code: 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 '('Worked more than', No_Hours, 'hours per day')      
FROM company.employees INNER JOIN c' at line 5

Yet it looks like I am using concat correctly, so how can I fix this problem? If I replace the problematic line with just:

AS N_Hours_Worked_Per_Day

then it works fine, but isn't as nice.

The expected output I want would look something like this:

Employee_Dilligence(2)

#COLUMN NAMES OF OUTPUT:

EmpName | EmpID | Hours_Worked | Worked more than 2 hours per day

Employee_Dilligence(3)

#COLUMN NAMES OF OUTPUT:

EmpName | EmpID | Hours_Worked | Worked more than 3 hours per day

CodePudding user response：

You can't use CONCAT as an alias for a field in MySQL. The alias must be a string, this will be the field's name in your result.

To build an SQL that include a variable as a fieldname, you can use a prepared statement with the following:

DELIMITER $$

CREATE PROCEDURE Employee_Dilligence(IN No_Hours INT)
BEGIN
    SET @sql = CONCAT('SELECT EmpName,employees.EmpID, Hours_Worked, Dilligence(Hours_Worked/(TIMESTAMPDIFF(Day, StartDate, CURDATE())*',No_Hours,')) AS "Worked more than', No_Hours, 'Hours" FROM company.employees INNER JOIN company.employee_project INNER JOIN company.projects ON employees.EmpID = employee_project.EmpID AND employee_project.ProjID = projects.ProjID WHERE projects.ProjID = ProjectID;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

With this you first make your query as a string, then make a prepared statement from it, which can be executed by the server.

CodePudding user response：

It seems you are doing a syntax error. please see blow how to use concate in query.

SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;