This discussion is archived
4 Replies Latest reply: Nov 30, 2008 12:10 AM by 673073 RSS

creating a table alias

673073 Newbie
Currently Being Moderated
I wonder if there is a way to create a table alias for a certain table ?

For example, suppose I have created my table this way.

CREATE TABLE MY_USER
( user_id VARCHAR(15) NOT NULL,
     password     VARCHAR(15),      
     PRIMARY KEY(user_id));

and now I would like to create an alias at the select statement

select *
from MY_USER as "newalias"
where newalias.user_id ="10";

Whenever I try to create the alias this way I get an error

SQL> select *
2 from MY_USER as "newalias"
3 where newalias.user_id ="10";
from MY_USER as "newalias"
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

I wonder what the right syntax is for that? Thank you so much.
  • 1. Re: creating a table alias
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    What ever you are trying is wrong.
    Second this Table Alias is provided in order to differentaite when you have gone for any self joins on same table and easy of use.

    You can not create an Alias like that. Might be you are thinking of Synonyms.

    create synonym
    < synonym-name>
    for object-name;

    create public synonym
    <synonym-name>
    for object-name;


    Example: -

    create table test1 (a number);

    create synonym t1 for test1;

    Refer : http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm

    - Pavan Kumar N

    Edited by: Pavan Kumar on Nov 30, 2008 9:23 AM
  • 2. Re: creating a table alias
    673073 Newbie
    Currently Being Moderated
    ok, I do use it in the join case, here it is. My problem is that, sql does not accept my aliasing.

    SELECT aptname, manager_id
    FROM MANAGER_UNITS AS "M", LEASE AS "L", PAYMENT AS "P"
    WHERE M.aptname =L.aptname AND M.unit# = L.unit AND P.rid = L.rid;


    so my aliases are M and L and P. But for some reason this does not work. What is incorrect? Thank you

    Edited by: user10653046 on Nov 29, 2008 8:20 PM
  • 3. Re: creating a table alias
    Justin Cave Oracle ACE
    Currently Being Moderated
    Why are you putting your aliases in double-quotes? Quoting any identifier in Oracle makes that identifier (alias, table name, column name, etc) case sensitive. If your alias contains non-uppercase letters, every time you reference that identifier, you would have to put the identifier in double quotes and get the casing correct.

    99+% of the time, the best answer is just to use Oracle's default case insensitivity and not double-quote identifiers.

    Justin
  • 4. Re: creating a table alias
    673073 Newbie
    Currently Being Moderated
    I figured it out. It apears that I just have to skip "AS" then it will work. Also, I am going to drop the "" for simplicity. Thank you!

    SELECT aptname, manager_id
    FROM MANAGER_UNITS M, LEASE L, PAYMENT P
    WHERE M.aptname =L.aptname AND M.unit# = L.unit AND P.rid = L.rid;


    now this works for me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points