What is wrong with my SQL code on CONCAT?

Question

CREATE VIEW ‘CustomerView’ AS SELECT
FROM ‘Customer’
ORDER BY ‘CustomerID’, CONCAT(‘FirstName,’ ‘ ’, ‘LastName’) AS ‘CustomerName’, ‘StreetAddress’, ‘Apt’, ‘City’, ‘State’, ‘ZipCode’, ‘HomePhone’, ‘MobilePhone’, ‘OtherPhone’
;

I am getting this error:

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 'FROM ‘Customer’ ORDER BY ‘CustomerID’, CONCAT(‘FirstName,’ ‘ ’, ' at line 2

SELECT **what** FROM ‘Customer’? – dnoeth May 27 '17 at 20:10 — dnoeth, May 27 '17 at 20:10

developer_hatch · Answer 1 · 2017-05-27T21:12:38.530

2

You missed a comma

ORDER BY ‘CustomerID’, CONCAT(‘FirstName,’, ‘ ’, ‘LastName’) AS  .....

Another way to write this more simple is adding the space after Firstname:

ORDER BY ‘CustomerID’, CONCAT(‘FirstName, ’, ‘LastName’) AS  .....

update

Also:

CREATE VIEW ‘CustomerView’ AS SELECT **"......"**

You didn't say what columns you want to select, if you want to select all columns, just add *

CREATE VIEW CustomerView AS 
SELECT CONCAT(FirstName, ' ', LastName) AS CustomerName,
...
...
FROM `Customer`
ORDER BY `CustomerID`;

edited May 27 '17 at 21:12

answered May 27 '17 at 20:09

developer_hatch

15,898
3
42
75

Thank you! I added * after SELECT and took your advice to the Order by statement and now I am getting a different error. 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 'AS ‘CustomerName’, ‘StreetAddress’, ‘Apt’, ‘City’, ‘State’,' at line 3 – Colleen Norton May 27 '17 at 20:41
CREATE VIEW ‘CustomerView’ AS SELECT * FROM ‘Customer’ ORDER BY ‘CustomerID’, CONCAT(‘FirstName, ’, ‘LastName’) AS ‘CustomerName’, ‘StreetAddress’, ‘Apt’, ‘City’, ‘State’, ‘ZipCode’, ‘HomePhone’, ‘MobilePhone’, ‘OtherPhone’ ; – Colleen Norton May 27 '17 at 20:46
@ColleenNorton after the AS, you are trying to select the columns names? – developer_hatch May 27 '17 at 21:00

score 2 · Accepted Answer · answered May 27 '17 at 21:00

It looks like you're using smart-quotes around your table and column identifiers. You should never use smart-quotes in code. There is no coding language that uses those characters.

You wouldn't use single-quotes around identifiers either. MySQL uses back-quotes by default for identifiers. Single-quotes are used for string literals or date literals in SQL.

Back-quotes look like this:

``

Single-quotes look like this:

''

Also you seem to have put your columns after the ORDER BY clause. I think you need to learn the syntax of SELECT.

I also saw an error in your CONCAT(). You got a comma inside the quotes when it should have been outside the quotes. You have this:

CONCAT(‘FirstName,’ ‘ ’, ‘LastName’)

It should be like this:

CONCAT(`FirstName`, ' ', `LastName`)

You would write the view like this:

CREATE VIEW `CustomerView` AS 
  SELECT CONCAT(`FirstName`, ' ', `LastName`) AS `CustomerName`, 
  `StreetAddress`, 
  `Apt`, 
  `City`, 
  `State`, 
  `ZipCode`, 
  `HomePhone`, 
  `MobilePhone`, 
  `OtherPhone`
FROM `Customer`
ORDER BY `CustomerID`;

The back-quotes are optional in your example. They are needed if your identifiers conflict with SQL reserved words or if the identifiers contain spaces or special characters (punctuation, international symbols, etc.). None of these cases apply in your example. So you can write it without back-quotes just fine:

CREATE VIEW CustomerView AS 
  SELECT CONCAT(FirstName, ' ', LastName) AS CustomerName, 
  StreetAddress, 
  Apt, 
  City, 
  State, 
  ZipCode, 
  HomePhone, 
  MobilePhone, 
  OtherPhone
FROM Customer
ORDER BY CustomerID;

Thank you. The quotes are a pain. The columns in my Order By is the way uCertify has us do in MySQL Workbench Lab. — Colleen Norton, May 27 '17 at 21:20

What is wrong with my SQL code on CONCAT?

2 Answers2