Re: Can I use an alias name is a where clause
Date: Wed, 9 Mar 2005 19:06:36 -0500
Message-ID: <lPydnVFQioQQErLfRVn-qA_at_comcast.com>
"Lig" <lignite_at_iol.ie> wrote in message
news:pgLXd.49194$Z14.37780_at_news.indigo.ie...
> The following select statement yeilds an error message ORA-00904:
> "Total": invalid identifier.
>
> select t.a + t.b + t.c Total
> from some_table t
> where Total > 0
>
> Why can't the alias Total be used in the Where clause?
>
> Is there any workaround other than doing something horrible like below
>
> select *
> from (select t.a + t.b + t.c Total
> from some_table t)
> where Total > 0
>
>
>
> Cheers
> Lig
it's not all that horrible...
consider the following:
SQL> create index fbi_ttl_com on emp(sal+nvl(comm,0));
Index created.
SQL> set autotrace on
SQL> select ename, ttl_comp
2 from
3 (
4 select ename, sal+nvl(comm,0) as ttl_comp 5 from emp
6 )
7 where ttl_comp = 800;
ENAME TTL_COMP
---------- ----------
smith 800
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=12) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=1 Bytes=12)
2 1 INDEX (RANGE SCAN) OF 'FBI_TTL_COM' (INDEX) (Cost=1 Card =1)
++ mcs Received on Thu Mar 10 2005 - 01:06:36 CET