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

Answer 1

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;

Answer 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`;