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_at_yasure>
>
> 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
ERROR at line 2:
ORA-00904: "TOTAL": invalid identifier
Date: Thu, 10 Mar 2005 08:32:44 -0800
Message-ID: <1110472168.435027_at_yasure>
Serge Rielau wrote:
> DA Morgan wrote:
>
[Quoted] >> Lig wrote: >> [Quoted] >>> 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 - 17:32:44 CET