Re: How would I write this sql

From: Aspeda <support_at_aspeda.com>
Date: Sat, 07 Jun 2003 03:58:45 GMT
Message-ID: <o2q2ev4hle636td0uq5e6k8lbbqsd7hiou@4ax.com>

On Fri, 6 Jun 2003 21:38:40 -0500, "Jake" <oracle-news_at_heyjay.com> wrote:

>How can I write this sql?
>
>SQL> SELECT user.id
> 2 FROM organization user
> 3 WHERE userid = 'myname'
> 4 /
>SELECT user.id
> *
>ERROR at line 1:
>ORA-00923: FROM keyword not found where expected
>
>I'm not able to change the table alias. And I know the error is from "user"
>being a pseudo column.
>
>but is there some sort of quoting, or something I could do, and leave the
>table alias?
>
>Thanks
>Jay
>

If you can't change the alias ... then here's one way that I was able to make it work (requires adding double-quotes):

SQL> select user.id
  2 from (select 1 id from dual) user
  3 where 1=1;
select user.id

           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> ed
Wrote file afiedt.buf

  1 select "user".id
  2 from (select 1 id from dual) user
  3* where 1=1
SQL> /
from (select 1 id from dual) user

                             *

ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> ed
Wrote file afiedt.buf

  1 select "user".id
  2 from (select 1 id from dual) "user"   3* where 1=1
SQL> /         ID

---

         1

Regards,
Vitaliy
DBAToolZ.com (free sql scripts + SQLDIR parser) Aspeda.com (free Oracle monitoring tool) Received on Fri Jun 06 2003 - 22:58:45 CDT