Re: Can I use an alias name is a where clause

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 9 Mar 2005 19:06:36 -0500
Message-ID: <lPydnVFQioQQErLfRVn-qA@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 Wed Mar 09 2005 - 18:06:36 CST