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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 10 Mar 2005 08:32:44 -0800
Message-ID: <1110472168.435027@yasure>

Serge Rielau wrote:
> DA Morgan wrote:
>

>> 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>

>
> combining two answers:
> > Serge ... please get yourself a copy of Oracle. Your answer is incorrect
> > because you assume, incorrectly, the syntax is invalid. The SQL
> > statement works just fine and the OP's problem is not syntactic.
>
> careful with the irony.
> Please read the OPs post again. He knows that this second statement
> works (he just doesn't like it).
>
> Until I get Oracle cleared with legal, try his first statement (his REAL
> issue):
>
> select t.a + t.b + t.c Total
> from some_table t where Total > 0
>
> Then, teacher, tell us whether it works, and if not why not.
> If you still are convinced that my answer is incorrect, please educate
> me on why.
>
> Tom Kyte shall be our judge whether the you, the Oracle expert, is
> correct or I, the SQL theorist ;-)
>
> Cheers
> Serge

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Mar 10 08:31:28 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> select t.a + t.b + t.c Total

   2 from some_table t where Total > 0; from some_table t where Total > 0

                         *

ERROR at line 2:
ORA-00904: "TOTAL": invalid identifier

How can legal possibly prevent you from having, on your own personal machine in your house, a copy of a product ... well other than fear that it is superior to their own offering. ;-)

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

Received on Thu Mar 10 2005 - 10:32:44 CST