CODEWITHSUNDEEP
mysql
Vipul sharma
Vipul sharma

Reputation: 1255

Subquery is not working on mysql (version 5.7.18)

This query is not working on mysql v 5.7.18 .

But this query was working perfectly in the past version of mysql (5.5.46)

SELECT car_id FROM (
                    SELECT car_id ,
                      CASE  WHEN ( unix_timestamp(STR_TO_DATE( car_from, '%Y-%m-%d %H:%i:%s' ))  > unix_timestamp(STR_TO_DATE( '2017-03-15 13:00', '%Y-%m-%d %H:%i:%s' ))
                                  || unix_timestamp(STR_TO_DATE( '2017-03-09 17:00', '%Y-%m-%d %H:%i:%s' )) > unix_timestamp(STR_TO_DATE(  car_to , '%Y-%m-%d %H:%i:%s' ))
                                  || unix_timestamp(STR_TO_DATE( '2017-03-15 13:00', '%Y-%m-%d %H:%i:%s' )) < unix_timestamp(STR_TO_DATE( '2017-03-09 17:00', '%Y-%m-%d %H:%i:%s' ))
                                  || unix_timestamp(STR_TO_DATE(  car_to, '%Y-%m-%d %H:%i:%s' )) < unix_timestamp(STR_TO_DATE(  car_from, '%Y-%m-%d %H:%i:%s' ))
                                )  THEN 0
                    ELSE 1 END as intersect_time
                    FROM car_booking_master
                    WHERE state = '1'
                    GROUP BY car_id
                    HAVING intersect_time =  1
                    ) AS virtual

On the new mysql version I am getting this error 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'virtual
LIMIT 0, 25' at line 15

Upvotes: 1

Views: 120

Answers (2)

Vipul sharma
Vipul sharma

Reputation: 1255

The problem on mysql version was the keyword I am using "virtual" . After using "v" in place of "virtual" query is working fine

Upvotes: 2

HD-it
HD-it

Reputation: 91

If you try to give your subquery a alias, you should remove AS in your last line.

Upvotes: 0

Related Questions