21

I'm trying to execute this query in Oracle SQL Developer:

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest AS G
  JOIN Stay AS S ON G.Guest_ID = S.Guest_ID
WHERE G.City = 'Miami' AND S.Room = '222';

However, I get the following error:

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 2 Column: 12

I don't see any problem in line 2 and the error is not very descriptive. It appears to be something to do with the as keyword. If I remove it, it works fine. However, I want my queries to be very verbose. Therefore, I must figure out a way to fix whatever the problem is without removing the as keyword.

This is the structure of the tables involved:

CREATE TABLE GUEST
(
  GUEST_ID       NUMBER               NOT NULL,
  LAST_NAME      VARCHAR2(50 BYTE),
  FIRST_NAME     VARCHAR2(50 BYTE),
  CITY           VARCHAR2(50 BYTE),
  LOYALTY_NUMBER VARCHAR2(10 BYTE)    
);

CREATE TABLE STAY
(
  STAY_ID        NUMBER                         NOT NULL,
  GUEST_ID       NUMBER                         NOT NULL,
  HOTEL_ID       NUMBER                         NOT NULL,
  START_DATE     DATE,
  NUMBER_DAYS    NUMBER, 
  ROOM           VARCHAR2(10 BYTE)
);

Thanks for any help in advance.

Marcos
  • 527
  • 1
  • 5
  • 12

2 Answers2

35

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it's always optional and on Oracle it's illegal.

So remove the AS :

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G
Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
  • I want the query to have the `as` keyword like I do in my MSSQL queries. – Marcos Jan 15 '14 at 18:09
  • 2
    @Marcos Oracle isn't MSSQL – Denys Séguret Jan 15 '14 at 18:10
  • Oracle has the `as` keyword. Why can't I use it? – Marcos Jan 15 '14 at 18:11
  • You can use it for field aliasing. – Denys Séguret Jan 15 '14 at 18:11
  • I don't find Oracle's documentation for table aliasing but [this one should do](http://www.toadworld.com/platforms/oracle/w/wiki/4761.tables-and-column-aliases.aspx). – Denys Séguret Jan 15 '14 at 18:14
  • I guess this is correct. This is what I get for learning the Microsoft way first. – Marcos Jan 15 '14 at 18:17
  • 5
    Oracle's documentation for the `SELECT` [*table_reference*](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55230) does **not** show `AS` before *t_alias*; compare with [*select_list*](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55229), which clearly shows the (optional) `AS` before *c_alias*. So the documentation does say it is allowed for column alias, and does not say it's allowed for table aliasing; therefore it isn't. – Alex Poole Jan 15 '14 at 18:47
  • @Marcos If your problem is to have the same queries both on MsSQL and Oracle, remove the AS completely from table aliasing. It's optional on MsSQL. – Denys Séguret Jan 16 '14 at 08:01
  • I saw a [video](https://youtu.be/aI0NpZsrui4?t=2m56s) and it shows that Oracle SQL Developer has the `AS` keyword – Dylan Czenski Dec 30 '15 at 14:22
  • 1
    @DylanChen yes, for column aliasing, not for table aliasing. – Denys Séguret Dec 30 '15 at 14:33
  • 1
    Oracle is non-compliant with the standard in this case. Using `AS` for a table alias is standard SQL, and is supported in most implementations. I never found a reason for why Oracle does not support it. I guess they always had something higher priority to work on, for the past 40 years! – Bill Karwin Nov 21 '22 at 20:08
  • @Ronald AS keyword for table aliasing is present in SQL:1992 standard. – 4LegsDrivenCat Mar 13 '23 at 17:21
  • @4LegsDrivenCat Thank you for pointing that out. I've deleted my post as I don't want to spread misinformation. – Ronald Mar 14 '23 at 13:15
1

Omit the AS for table alias in Oracle.

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G
  JOIN Stay S ON G.Guest_ID = S.Guest_ID
WHERE G.City = 'Miami' AND S.Room = '222';

This will give you the output without errors.

Cà phê đen
  • 1,883
  • 2
  • 21
  • 20
Kokila Mohan
  • 11
  • 1