Why Does My Oracle SQL Alias Fail When Using 'AS'?

Oracle SQL table alias: omit "as" keyword

When using Oracle SQL, it is important to understand the correct usage of the "as" keyword in table aliases. Unlike many other SQL servers (such as MySQL and PostgreSQL), Oracle considers the use of the "as" keyword in a table alias illegal.

In the query you provided, you encountered an error when using the "as" keyword to create an alias for the "Guest" table:

<code>ORA-00933: SQL command not properly ended</code>

To resolve this error and ensure that the query executes correctly, simply remove the "as" keyword as shown below:

<code>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';</code>

Table structure:

For reference, here is the structure of the relevant table:

<code>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)
);</code>

By omitting the "as" keyword in the query, you can effectively create an alias for the table while adhering to Oracle's syntax requirements.

The above is the detailed content of Why Does My Oracle SQL Alias Fail When Using 'AS'?. For more information, please follow other related articles on the PHP Chinese website!