Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use the 'as' keyword to alias a table in Oracle?

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.

like image 263
Marcos Avatar asked Jan 15 '14 18:01

Marcos


People also ask

Can I use AS in Oracle SQL?

Both are correct. Oracle allows the use of both.

Which keyword is used to alias the table?

SQL AS keyword is used to give an alias to table or column names in the queries.

Do you need to use AS in SQL for alias?

SQL allows the use of both column aliases and table aliases. This is done using the SQL AS keyword, which is an optional keyword in many SQL statements including SELECT, UPDATE, and DELETE. Column aliases allow you to use a different and often simpler name for a column in your query.

Does Oracle support as Alias?

Description. Oracle ALIASES can be used to create a temporary name for columns or tables. COLUMN ALIASES are used to make column headings in your result set easier to read.


1 Answers

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
like image 53
Denys Séguret Avatar answered Sep 18 '22 00:09

Denys Séguret