Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Functions and where vs. group-by inconsistency
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?
Second, and relating to this I am confused as to how ORACLE handles
group-by clauses with functions. (Or rather I find it a bit
inconsistent.)
a)
select my_func(col1), col2
from a_table
where my_func(col1) = :filter;
==> This will evaluate my_func() two(2) times for every row in the
table.
b)
select my_func(col1), sum(col2)
from a_table
group by my_func(col1);
==> This will NOT evaluate the function twice(2) for every result row,
instead it is EXACTLY the same as when I would write:
c = b)
select col1_func, sum(col2)
from (select my_func(col1) col1_func, col2)
group_by col1_func
Consider also the attached example to further confuse the issue.
Any opinions, pointers, corrections on this are most welcome.
cheers,
Martin
CREATE OR REPLACE
FUNCTION TEST_PUT(x IN VARCHAR2)
RETURN VARCHAR2 IS
ret VARCHAR2(100) := x || test_package.global_counter;
BEGIN
dbms_output.put_line(ret);
test_package.global_counter := NVL(test_package.global_counter, 0) +
1;
return ret;
END;
/
![]() |
![]() |