Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable.

The newly created view works properly and all SQL is correct.

This is the error message I get when I try to view all records from the view (or when I click on "Browse" in phpMyAdmin):

#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 'THEN0 ELSE1 END LIMIT 0, 30' at line 1

Here is example schema:

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `comments` (`id`, `user_id`, `title`) VALUES
(1, 1, 'user one comment 1'),
(2, 1, 'user one comment 2'),
(3, 2, 'user two comment 1'),
(4, 2, 'user two comment 2');

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2');

Sample view:

CREATE VIEW view_sample AS
SELECT
  c.id AS comment_id,
  c.title AS comment_title,
  u.name AS user_name
FROM comments c, users u
WHERE c.user_id = u.id

At this point you can verify that the view above works properly and shows results.

Sample query that will break everything:

SELECT * FROM view_sample
WHERE comment_title LIKE 'user one%'
ORDER BY CASE WHEN comment_title LIKE 'user one%' THEN 1 ELSE 0 END, comment_title

Query above will work, but whatever you try to do afterwards will fail. Try simply this:

SELECT * FROM view_sample

I tried this several times, with different views.

What am I doing wrong?

share|improve this question
    
Can you paste the exact code you're using? From the error posted it looks like you don't have a space between THEN and 0 (and ELSE and 1, for that matter). –  Simon Righarts Nov 12 '13 at 11:08
    
Thanks for the message; I will update the answer shortly with simplified example code. My original view works properly and there are no syntax errors. –  Ivanhoe123 Nov 12 '13 at 11:39
    
I added sample SQL for creating necessary tables and steps to reproduce the issue. –  Ivanhoe123 Nov 12 '13 at 11:49
1  
This is the kind of thing you should test in the MySQL command line client, because it sounds like phpmyadmin, which is 3rd party software, might be breaking your query, if I understand the issue as you describe it, as opposed to a problem in MySQL. –  Michael - sqlbot Nov 12 '13 at 12:41
add comment

3 Answers

1)Syntax error: need add space between THEN and 0 (eq. ELSE).

share|improve this answer
    
Thanks for the answer, but it's not the case. View is created properly and the syntax for creating is correct. I am preparing now sample code so I can show my point. –  Ivanhoe123 Nov 12 '13 at 11:38
    
Sample code is added to the original post. –  Ivanhoe123 Nov 12 '13 at 11:50
    
this is code good working , this result log(mysql server 5.6): 0:23:28 2 Query select * from view_sample –  Vfvtnjd Nov 12 '13 at 12:23
    
Is it possible that this is related to my setup? I'm testing this on MAMP and phpMyAdmin that is shipped with it. Version of MySQL is 5.5.29. –  Ivanhoe123 Nov 12 '13 at 12:34
add comment

I tried your schema / query on SqlFiddle with:

MySql 5.5.32 (show here)

MySql 5.6.6 m9 (show here)

and work fine together.

Please check your PhpMyAdmin possibly fault

share|improve this answer
add comment

Are you talking about the error as shown in this SQL Fiddle?

When you add semi-colons, do you get results from the first query and none from the second like in this SQL Fiddle?

If you are expecting results like in this SQL Fiddle, you will likely need to use a different client. The difference is the batch terminator.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.