4 Replies Latest reply: Nov 30, 2008 2:10 AM by 673073 RSS

    creating a table alias

    673073
      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
          Pavan Kumar
          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
            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
              JustinCave
              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
                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.