ROWNUM and Aggregate Functions

When working on aggregate functions, you must be aware of rownum. Resultset firstly taken with rownum and then aggregate functions are processed.
Look at example below:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL>
SQL> drop table testtab;
Table dropped
SQL> CREATE TABLE testtab AS  SELECT o.object_name n, mod(rownum, 5) i, rownum r FROM all_objects o WHERE rownum < 11;
Table created
SQL> SELECT * FROM testtab;
N                                       I          R
------------------------------ ---------- ----------
ICOL$                                   1          1
I_USER1                                 2          2
CON$                                    3          3
UNDO$                                   4          4
C_COBJ#                                 0          5
I_OBJ#                                  1          6
PROXY_ROLE_DATA$                        2          7
I_IND1                                  3          8
I_CDEF2                                 4          9
I_PROXY_ROLE_DATA$_1                    0         10
10 rows selected
SQL> SELECT * FROM testtab WHERE i = 0;
N                                       I          R
------------------------------ ---------- ----------
C_COBJ#                                 0          5
I_PROXY_ROLE_DATA$_1                    0         10
SQL> SELECT max(r) FROM testtab WHERE i = 0 and rownum = 1;
    MAX(R)
----------
         5
SQL> SELECT max(r) FROM testtab WHERE i = 0;
    MAX(R)
----------
        10
SQL> SELECT * FROM (SELECT max(r) FROM testtab WHERE i = 0)  WHERE rownum = 1;
    MAX(R)
----------
        10
SQL>

Aggregation Functions and NO_DATA_FOUND Exception In Oracle

here are some important points when using aggregation functions. Although you have no data, this functions(MAX, MIN etc.) return  row and if you add NO_DATA_FOUND exception, exception block never executes.

Aggregation functions al least return one record when your table is empty except

• when you use HAVING clause
• when you use GROUP BY expression

Otherwise they return one NULL record.


I demonstrate a small example to make clear:

DECLARE
a VARCHAR2(12) := '';
b NUMBER;
BEGIN

SELECT COUNT(a.dummy)
INTO b
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('COUNT(a.dummy) without MAX is ' || b);

SELECT COUNT(*)
INTO b
FROM (SELECT MAX(a.dummy)
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T');
dbms_output.put_line('COUNT(a.dummy) with MAX is ' || b);

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('MAX(a.dummy)[No HAVING, No GROUP BY] passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) [No HAVING, No GROUP BY]...');
END;

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
HAVING MAX(a.dummy) = 'T';
dbms_output.put_line('MAX(a.dummy) with HAVING passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) with HAVING...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T'
GROUP BY a.dummy;
dbms_output.put_line('a.dummy with group by passed..');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy with group by...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('a.dummy passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy...');
END;

END;




The output is

COUNT(a.dummy) without MAX is 0
COUNT(a.dummy) with MAX is 1
MAX(a.dummy)[No HAVING, No GROUP BY] passed..
no_data_found exception in MAX(a.dummy) with HAVING...
no_data_found exception in a.dummy  with group by...
no_data_found exception in a.dummy...

Oracle Set Operators

Oracle küme operatörleri 4 tanedir. union, union all, minus ve intersect. Oracle küme işlemleri yapılırken select cümlelerinin aynı tip ve sayıda kolon içermelerine dikkat edilmelidir. Eğer bir kümede bulunan kolon diğer kümede yok ise, o kolon NULL ile gösterilmelidir. Sütün isimlerini aynı olmasına gerek yoktur. Set operatörleri ile işlem yaparken ORDER BY varsayılan olarak olarak ORDER BY 1 ASC'dir. Yani ilk kolon artan olarak sıralanacaktır.

Bunları bir örnek ile anlatmaya çalışalım. Öncelikle iki küme oluşturup işlemlerimizi onlar üzerinde yapalım.

create table a( x number);
create table b( x number);

BEGIN
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO a VALUES (4);
INSERT INTO b VALUES (3);
INSERT INTO b VALUES (4);
INSERT INTO b VALUES (5);
INSERT INTO b VALUES (6);
INSERT INTO b VALUES (7);
COMMIT;
END;

SELECT * FROM a
--1,2,3,4

SELECT * FROM b
--3,4,5,6,7
union : İki kümede tekrar etmeyen verileri getirir. Matematiksel karşılığı (a+b)'dir

-- (a + b)
--No duplicates
SELECT x
FROM a
UNION
SELECT x FROM b
--1,2,3,4,5,6,7
union all : İki kümede verilerin tamamını getirir. Matematiksel karşılığı (a)+(b)'dir

-- (a) + (b)
--Duplicates
SELECT x
FROM a
UNION ALL
SELECT x FROM b
--1,2,3,4,3,4,5,6,7
minus : Sadece ilk kümede olan elemanları getirir. Matematiksel karşılığı (a) - (b)'dir

-- (a) - (b)
--Difference
SELECT x
FROM a
MINUS
SELECT x FROM b
--1,2
intersect : İki kümede kesişen elemanları getirir. Matematiksel karşılığı (a) + (b) - ( a + b)'dir

-- ( ( (a) + (b) ) - ( a + b) )
--intersect
SELECT x
FROM a
INTERSECT
SELECT x FROM b
--3,4