SQL error in a MySQL query with alias (AS)

Question

I had problem while executing this SQL statement in MySQL workbench. Can anyone point out the problem for me?

SELECT concat(LocationName,' - ', StationName) AS LocationStation, EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200', FROM satsschema.employeeslot
WHERE LocationName = 'T2 PML'
and AllocationDate = '10-Aug' 
and (EmpTime >= '00:00:00' and EmpTime <= '12:00:00')

The above statemnt prompted me 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 neat 'FROM satsschema.employeeslot WHERE LocationName = 'T2 PML' and AllocationDate = ' at line 1."

Secondly, Is there any other ways that could make this line shorter besides typing everything out?

EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200', FROM satsschema.employeeslot

Help will be greatly appreciated.

What's the point in giving more than one alias to one column ? — Denys Séguret
– Denys Séguret, Commented Aug 29, 2012 at 6:32
The 1hour break time indicates the header and the values inside will be the employees that were working in the time slot. — Philemon
– Philemon, Commented Aug 29, 2012 at 6:35

Denys Séguret · Accepted Answer · 2012-08-29 06:34:05Z

1

Remove the comma before FROM. It causes the error.

SELECT concat(LocationName,' - ', StationName) AS LocationStation, EmpName AS '0000-0100', EmpName AS '0100-0200', EmpName AS '0200-0300', EmpName AS '0300-0400', EmpName AS '0400-0500', EmpName AS '0500-0600', EmpName AS '0600-0700', EmpName AS '0700-0800', EmpName AS '0800-0900', EmpName AS '0900-1000', EmpName AS '1000-1100', EmpName AS '1100-1200' FROM satsschema.employeeslot
WHERE LocationName = 'T2 PML'
and AllocationDate = '10-Aug' 
and (EmpTime >= '00:00:00' and EmpTime <= '12:00:00')

Note that the error message said exactly where was the error.

In MySQL, the AS keyword is optional for column aliasing, so you may write EmpName '0000-0100' in place of EmpName AS '0000-0100'. I don't think there is a short solution to give more than one alias to one column as it's really not something that is often necessary.

edited Aug 29, 2012 at 6:34

answered Aug 29, 2012 at 6:28

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philemon Over a year ago

Yep I understand but giving more then 1 alias in this case for me is to let the user know which employees falls inside the time slot. It's like a schedule.

John Woo · Accepted Answer · 2012-08-29 06:33:37Z

0

Remove extra comma before from clause.

SELECT  concat(LocationName,' - ', StationName) AS LocationStation, 
        EmpName AS '0000-0100', 
        EmpName AS '0100-0200', 
        EmpName AS '0200-0300', 
        EmpName AS '0300-0400', 
        EmpName AS '0400-0500', 
        EmpName AS '0500-0600',
        EmpName AS '0600-0700', 
        EmpName AS '0700-0800', 
        EmpName AS '0800-0900', 
        EmpName AS '0900-1000', 
        EmpName AS '1000-1100',
        EmpName AS '1100-1200'   -- remove comma here
FROM satsschema.employeeslot
WHERE LocationName = 'T2 PML'
and AllocationDate = '10-Aug' 
and (EmpTime >= '00:00:00' and EmpTime <= '12:00:00')

answered Aug 29, 2012 at 6:33

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

SQL error in a MySQL query with alias (AS)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related