Solved

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

Posted on 2016-10-03
2
81 Views
Last Modified: 2016-10-03
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
0
Comment
Question by:Eduardo Fuerte
2 Comments
 
LVL 22

Accepted Solution

by:
Kim Walker earned 500 total points
ID: 41826398
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.
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 41826432
Perfect!

Thank you for suggestions also.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question