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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 09 Mar 2005 15:38:12 -0800
Message-ID: <1110411299.739175_at_yasure>


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

[Quoted] 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)
Received on Thu Mar 10 2005 - 00:38:12 CET

Original text of this message