Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Functions and where vs. group-by inconsistency

Functions and where vs. group-by inconsistency

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 6 Oct 2006 04:00:45 -0700
Message-ID: <1160132445.250361.96430@k70g2000cwa.googlegroups.com>


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;
/

Received on Fri Oct 06 2006 - 06:00:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US