Solved

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

Posted on 2016-10-03
2
48 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 21

Accepted Solution

by:
Kim Walker earned 500 total points
Comment Utility
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
Comment Utility
Perfect!

Thank you for suggestions also.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now