0

I have a small database for a retail scenario, within this I have a field called "Dispatched" which is a bool to indicate if the item has been dispatched yet. This is of course as 1 and 0, I have attempted a simple CASE WHEN to make 1 display as Yes and 0 as No.

My full query is:

SELECT 
orders.OrdersID, 
stock.ItemName, 
basket.Quantity, 
customer.FirstName, 
customer.LastName, 
address.AddressLine1, 
address.AddressLine2, 
address.TownOrCity, 
address.Postcode, 
address.Country, 
CASE WHEN basket.Dispatched = 1 THEN 'Yes' ELSE 'No' END AS basket.Dispatched 
FROM orders
JOIN OrdersBasketJoin ON orders.OrdersID = OrdersBasketJoin.OrdersID
LEFT JOIN basket ON OrdersBasketJoin.BasketID = basket.BasketID
JOIN customer ON orders.CustomerID = customer.CustomerID
JOIN address ON orders.DeliveryAddress = address.AddressID
JOIN stock ON basket.StockID = stock.StockID
ORDER BY  `customer`.`CustomerID` ASC
LIMIT 0 , 30

The query works fine without the CASE WHEN, and will display the 1s and 0s when Dispatched is selected normally, as well as WHERE working fine when referencing Dispatched.

However when I try adding 

CASE WHEN basket.Dispatched = 1 THEN 'Yes' ELSE 'No' END AS basket.Dispatched

I get the 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 '.Dispatched FROM orders JOIN OrdersBasketJoin ON orders.OrdersID = Ord' at line 12

From what I've researched this is pretty much as simple of a CASE WHEN you can do, and the syntax is correct I believe.

Not sure if its just a visual bug, but "END" in the CASE doesn't light up like it is known to be a function, whereas JOIN, ON, LEFT etc all light up, no matter where END doesn't.

Any and all help is much appreciated -Tom

Improve this question

3 Answers 3

Reset to default
4

You are missing with , after address.Country therefore you are getting syntax error try this one 

SELECT 
orders.OrdersID, 
stock.ItemName, 
basket.Quantity, 
customer.FirstName, 
customer.LastName, 
address.AddressLine1, 
address.AddressLine2, 
address.TownOrCity, 
address.Postcode, 
address.Country, 
(CASE WHEN basket.Dispatched = 1 THEN 'Yes' ELSE 'No' END) AS `basket.Dispatched` 
FROM orders
JOIN OrdersBasketJoin ON orders.OrdersID = OrdersBasketJoin.OrdersID
LEFT JOIN basket ON OrdersBasketJoin.BasketID = basket.BasketID
JOIN customer ON orders.CustomerID = customer.CustomerID
JOIN address ON orders.DeliveryAddress = address.AddressID
JOIN stock ON basket.StockID = stock.StockID
ORDER BY  `customer`.`CustomerID` ASC
LIMIT 0 , 30
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I had a comma there initially, and was getting the 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 '.Dispatched FROM orders JOIN OrdersBasketJoin ON orders.OrdersID = Ord' at line 12
before FROM you don't need comma , you need comma to separate the columns in select list so AS basket.Dispatched is another new col so you need comma before that and then use from
@Tom see my answer again i have used backticks ` also around basket.Dispatched
Ah thank you, I was going from this other question: stackoverflow.com/questions/6008519/… and there it doesn't show the need for baskets or ``
1

Put backticks:

AS `basket.Dispatched`
Improve this answer

Comments

0 
CASE WHEN basket.Dispatched = 1 THEN 'Yes' ELSE 'No' END AS 'basket.Dispatched'

You are missing single quotes before and after the aliase name.

I have tried this in my DEV machine without the single quote and have the same error. 

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.