Re: Functions and where vs. group-by inconsistency

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 06 Oct 2006 19:29:16 -0700
Message-ID: <1160188153.356601@bubbleator.drizzle.com>

Martin T. wrote:

> DA Morgan wrote:
>> 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.  ;-)
>> --
> 
> I wouldn't go so far as to call it "far" too much :-)
> But while one is waiting for the compiler to finish, or the file to
> download, or other such thankless tasks, one may just as well try to
> find out why Oracle does things the way it does them -- or what it
> actually does ... for example that it will not evaluate functions in
> the group by clause if the same appers in the select expression.
> 
> Whatever - I really should start to ask less at once ... :-)
> 
> cheers,
> Martin

Then, perhaps, you can substitute this:

CREATE TABLE airplanes (
program_id VARCHAR2(3),
delivered_date DATE,

customer_id     VARCHAR2(4),
order_date      DATE);

CREATE TABLE ap_cust (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

CREATE TABLE ap_orders (
order_date DATE,
program_id VARCHAR2(3));

INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_date FROM airplanes;

It is far more interesting. At least to me. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Oct 06 2006 - 21:29:16 CDT