How to Use the 'As' Keyword to Alias a Table in Oracle

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

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

Is the 'as' keyword required in Oracle to define an alias?

According to the select_list Oracle select documentation the AS is optional.

As a personal note I think it is easier to read with the AS

How can I use an oracle keyword as an alias?

Use double quotes around the keyword:

SELECT DATA.EXT_ID AS "NUMBER" FROM DATA;

oracle: can you assign an alias to the from clause?

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

Most databases support omitting the AS keyword.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

The reason you need the distinct is because of the Cartesian product.

Oracle SQL - a hint clause and a table alias

Yes, the first hint appears to be syntactically valid (though presumably you can test that yourself) assuming that IDX01 is an index that exists on TableA and that it is possible for the query to use that index. I'm not a fan of having this sort of hint in production code since it generally implies that you're trying to cover up an underlying problem with statistics but it appears to be valid.

There is nothing wrong with using an alias that matches the table name. It's possible that someone added that alias to make the hint valid or because the query originally didn't have an alias and some of the columns in the select list or where clause were using the table_name.column_name syntax. Of course, there are other possibilities that we could speculate about. Someone in your organization may know more of the history or source control might show the evolution.

ORA-907 missing right parenthesis when using AS keyword

Your problem is the as. However, I think your query is intended to be a correlated subquery. This should be written as:

SELECT e.id, e.fk_easbware_id, e.mandant, e.reg_code_mc,   
       e.hazard_code_ident, e.add_hazard_code, e.haz_code_version,   
       e.undg_number, e.ship_flashpoint, e.flashpoint_type,   
       e.cont_dopc, e.cont_dop, e.cont_phone, e.verpack_grp_mc,   
       e.ems_nr, e.trem_card_nr, e.secondimo, e.thirdimo,
       (SELECT COUNT(*)
         FROM easbdgstn_t p ON 
         WHERE e.id = p.fk_easbwaredgsid AND
               p.type_mc = 'TRANSPORT_DGS_LIM_QUANT'
       ) AS cc_is_limited_quantities
FROM easbwaredgs_t e
WHERE e.mandant = '001' AND
      e.fk_easbware_id = 1;

Just to clarify: as is permitted (and desired) for column aliases. It is not allowed for table aliases in Oracle.

Oracle AS keyword and subqueries

The pattern for the SQL 99 ANSI is that the table can have an alias WITHOUT the AS keyword so, you can take out AS and it should work on every RDBMS. See it on fiddle:

• MySQL
• Oracle
• PostgreSql
• SQLLite
• SQLServer

In ISO/IEC 9075-2:1999, section 7.6 <table reference>, page 232:

<table reference> ::=
   <table primary>
   | <joined table>

<table primary> ::=
   <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
   | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <lateral derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <collection derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]
   | <only spec>
      [ [ AS ] <correlation name>
         [ <left paren> <derived column list> <right paren> ] ]
   | <left paren> <joined table> <right paren>

---

Also confirmed to work:

• MS Access (Jet)

---

Related Topics