Could you check what is preventing this MySQL query to correctly running?

Eduardo Fuerte
Eduardo Fuerte used Ask the Experts™
on
Hi Experts

Could you check what is preventing this MySQL query to correctly running?

SELECT
                id_reclamacao,
                sise,             
		fk_seguradora,
                nome_reclamante,
                data,
                case TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP))
                 WHEN <24 THEN 'V'
                 WHEN >=24 AND < 36 THEN 'A'
                 WHEN >=36 AND < 48 THEN 'L'
                 ELSE 'Ve' 
                 END AS sla,
                nome_agente,
                procedencia
FROM system_reclamacao

Open in new window


The error produced:
img001
Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Web Programmer/Technician
Commented:
The < comparison operator needs a value or field name before it. The direct approach would be to copy your TIMESTAMPDIFF expression into each condition as such:
CASE WHEN TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP)) < 24 THEN 'V'
	WHEN TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP)) >= 24 
		AND TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP))  < 36 THEN 'A'
	WHEN TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP)) >= 36 
		AND TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP))  < 48 THEN 'L'
	ELSE 'Ve' 
END AS sla

Open in new window

But this isn't very efficient as the TIMESTAMPDIFF would have to be calculated multiple times. I suggest creating a temporary table of TIMESTAMPDIFF values and then running the case command against it.
CREATE TEMPORARY TABLE `timediff` AS SELECT TIMESTAMPDIFF(HOUR, data, IFNULL(data_atendimento, CURRENT_TIMESTAMP)) AS `timespan` FROM `tablename` WHERE 1;
SELECT 
	CASE `timespan` < 24 THEN 'V'
		WHEN `timespan` >= 24 AND `timespan` < 36 THEN 'A'
		WHEN `timespan` >= 36 AND `timespan` < 48 THEN 'L'
		ELSE 'Ve' 
	END AS sla
FROM `timediff` WHERE 1;

Open in new window

To run this in PHP, you would run the create temporary table statement as a separate query before the select case statement. The temporary table would persist as long as the database connection is open. If you run this query often, you may want to create a view in your table which contains these time difference values persistently.
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Perfect!

Thank you for suggestions also.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial