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

Lig wrote:

> 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

What's your problem?

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Mar 9 15:36:41 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create table some_table (

   2  a NUMBER(1),
   3  b NUMBER(1),
   4  c NUMBER(1));

Table created.

SQL> insert into some_table values (1,1,1);

1 row created.

SQL> insert into some_table values (0,0,0);

1 row created.

SQL> commit;

Commit complete.

SQL> select *

   2 from (select t.a + t.b + t.c Total    3 from some_table t)
   4 where Total > 0;

      TOTAL

          3

SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)