2

This query is searching employee name and employee`s manager.

error

but 'as' keyword causes error ora-00933.

so, i changed "from employee as e, employee as s" to "from employee e, employee s".

why 'as' keyword causes the error ora-00933?

mjkim
  • 581
  • 3
  • 6
  • 19
  • 1
    Because you don't use the "as" keyword there. get rid of it. – OldProgrammer Oct 09 '14 at 16:20
  • it is puzzling - Oracle docs say 'as' is optional there. – n8wrl Oct 09 '14 at 16:21
  • 3
    Use `as` to alias columns, not tables. Here's a discussion.. https://github.com/r0man/sqlingvo/issues/4 – zundarz Oct 09 '14 at 16:23
  • so this is wrong query. in my book(database systems), uses 'as' to alias table. :( – mjkim Oct 09 '14 at 16:32
  • 1
    @Andrew - that documentation is for MySQL 5.0, not Oracle RDBMS. They are different products with different flavours of SQL. You've never been able to use as for a table alias in Oracle SQL. The Oracle RDBM docs are [here](http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#i2126863). The [`t_alias` part](http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#SQLRF55303) is relevant here, and does not show `AS`. – Alex Poole Oct 09 '14 at 16:46
  • @AlexPoole - Dang, I apparently need to go back to sleep or something... – Andrew Oct 09 '14 at 17:53

2 Answers2

7

The as keyword can be used in the select clause but not the from clause. This is the general idea:

select field1 as f1
from table1 t1
where t1.field1 = something
Andriy M
  • 76,112
  • 17
  • 94
  • 154
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • I'm not certain what versions are involved, but the Oracle docs here: http://docs.oracle.com/cd/E17952_01/refman-5.0-en/select.html clearly say 'as' is optional. tbl_name [[AS] alias] – n8wrl Oct 09 '14 at 16:32
  • 3
    @n8wrl - that documentation is for MySQL 5.0, not Oracle RDBMS. They are different products with different flavours of SQL. You've never been able to use `as` for a table alias in Oracle SQL. – Alex Poole Oct 09 '14 at 16:43
  • @AlexPoole: So sorry! It's been 15+ years since I've used Oracle. I should have kept my keyboard quiet :( – n8wrl Oct 09 '14 at 17:42
  • @n8wrl - things got a lot more confusing when Oracle bought Sun and got MySQL as part of the deal; and then put all the docs under the same site... – Alex Poole Oct 09 '14 at 17:44
  • I don't want to name anyone in public, but there are so many Oracle ACEs and ACE Directors who have managed to demonstrate the use of AS keyword in FROM clause on Oracle RDBMS, don't know how. Probably, the site is based on MySQL DB, and the test case was not executed on Oracle. A simple google search would show up all those sites ;-) – Lalit Kumar B Oct 09 '14 at 17:59
0

To be precise about an answer to the question, in ORACLE, to use the keyword AS before the alias, then remember : column names, yes, table names, no.

In other words, in select, yes, in from clause, no.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124