Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

919 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

18 Experts available now in Live!

Get 1:1 Help Now