Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-03
2
Medium Priority
?
98 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Kim Walker earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

670 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