srinivas

• Sep 15th, 2006

 

Yes definetly aliases can't be used in the WHERE clause.

But in case of pseudo columns, we can use aliases in the WHERE clause, Because these r not actual columns. whenever we create a table, then pseudo columns are created for help in the data manipulations. Pseudo columns are nownum, rowid and so on

Urs Srinivas

chitra

• Sep 18th, 2006

 

Thanks Srinivas

Hitesh

• Nov 2nd, 2006

 

I do not agree with srininvas.

SQL>  select * from (select rownum rnum, sal salary from emp a);

      RNUM     SALARY
---------- ----------
         1      10000
         2      30000
         3      40000
         4      50000
         5      60000
         6       2000
         7       2000
         8       2000
         9       2000

9 rows selected.

SQL> select * from (select rownum rnum, sal salary from emp a) where salary = 2000;

      RNUM     SALARY
---------- ----------
         6       2000
         7       2000
         8       2000
         9       2000

Lavanya Chowdary

• May 6th, 2007

 

If you are using in-line views in that query if you are using any aliases it will treat it as the column name for the outer query that's why it is working in the where clause

Praveen Vaddadi

 Profile Answers by Praveen Vaddadi Questions by Praveen Vaddadi

• Mar 6th, 2008

 

Lets examing the query as:
select * from (select rownum rnum,a.* from testsal a) b where b.rnum=3;".
The subquery here is used as another table for the main query and only its structure will be passed to main query irrespective of the subquery execution.
Hence an alias name used from an alias table works, while alias name taken from direct table doesn't work in the WHERE.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• Jun 21st, 2008

 

Alais names in where clause works with inline view only.