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

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 10 Mar 2005 20:28:49 -0800
Message-ID: <1110515134.71450@yasure>

Serge Rielau wrote:
> DA Morgan wrote:
>

>> Serge Rielau wrote:
>>
>>> DA Morgan wrote:
>>>
>>>> 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
>>>
>>>
>>>
>>> Very good. Can you now explain why Oracle - in full compliance with 
>>> the SQL standard - raises the error?
>>>
>>> My answer is on record.
>>>
>>> Cheers
>>> Serge
>>
>>
>>
>> No ... that question would better be directed to Mr. Townsend as (A) I
>> do not, can not, and will not speak for Oracle Corp. and (B) I really
>> couldn't care less as I think the standard is worthless.

>
> You need Mark to explain why the statement fails? Yet you had little
> symphathy for the original poster who asked why.... seems like he is a
> more inquisitive nature than you are, what's wrong with that?
>
> If Mark doesn't asnwer, meybe Don Deutsch, Fred Zemke or Jim Melton (all
> Oracle) will oblige.
>

>>  From where I sit here in my ivory tower at the university ... 

>
> Perhaps you should descend, walk across the courtyard to the tower of
> the database faculty (the real one that is) and ask: Why did this
> statement fail? Why can the IBM dude, in his arrogance, dare answer the
> Oracle question?
> BTW, I wouldn't rub that University stuff in.. I also taught at
> University: CPR ;-) There is a difference between teaching, and teaching
> a CS database class.
>
> > I dare any
>

>> vendor of any RDBMS to claim they are "in full compliance with the SQL
>> standard." Want to make that claim about DB2 ... go for it. And remember
>> that is not "partial" compliance ... that is "full" compliance: Your
>> words.

>
> Shoosh.. again you did not read.
> I noted that Oracle is raising this error in full compliance - and so
> will IDS, XPS, Teradata, and *crossmyfingersknockonwood* even Sybase and
> SQL Server.
>
> No ifs,
> No buts,
> No DB2 being better message hidden between the lines, ...
> Just acknowledging the basic laws of SQL at work.
>
> Simply accept that I do post useful messages, stick to your guns as I
> stick to mine and we can happily co-exist.
>
> Cheers
> Serge

Reading <> comprehension.

I assumed your statement, quoted below, was sarcastic.

 >>> Very good. Can you now explain why Oracle - in full compliance with  >>> the SQL standard - raises the error?

If the error is in full compliance the question seems without value. So even now, with a full reread, your statement makes no sense. I'll see if another glass of scotch helps.

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

Received on Thu Mar 10 2005 - 22:28:49 CST