Re: Functions and where vs. group-by inconsistency

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 06 Oct 2006 17:07:27 -0700
Message-ID: <1160179644.567264@bubbleator.drizzle.com>

Martin T. wrote:
> DA Morgan wrote:

>> Martin T. wrote:
>>> Hello all.
>>> (assuming Oracle 9.2)
>>>
>>> First of all, does anyone know why the Oracle SQL Parser will not allow
>>> us to use the <name>-part of "table.column AS <name>" in where or
>>> group-by clauses?? Is this just being lazy on their part, or is there
>>> an actual reason behind this?
>> Just read the above paragraph a second time. I have no idea what you
>> are asking. Is the lack of clarity just being lazy on your part? ;-)
>>

>
> Interestingly enough I posted exactly the same on the Oracle forums and
> people there had no problem understanding what I meant. Probably
> because they did not only read the 1st paragraph which may in fact be a
> bit unclear on my part.
>
> Why can I not do:
> select my_func(col1) AS col1_func
> from table
> where col1_func = :value;
> - or even -
> select my_func(col1) AS col1_func, sum(col2)
> from table
> group by col1_func
>
> For further discussions, if you are interested, see:
> http://forums.oracle.com/forums/thread.jspa?threadID=431012&tstart=15
>
> cheers,
> Martin

Because COL1_FUNC is an alias. It is not the name of a column.

You must have far too much idle time on your hands if this is the issue d'jour. ;-)

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Fri Oct 06 2006 - 19:07:27 CDT