SQL & PL/SQL

Hi,

I am trying to do something that is utterly meaningless, but I am studying the use of CAST with MULTISET and TABLE.

I have created a type: a Nested Table of Number values:

create type numTable as table of number;

Now I perform the following query:

select d.dname
, ( select avg(column_value)
from table
( cast( d.salaries as numTable)
)
) Department_Avg_Salary
from ( select cast
( multiset
( select e.sal
from emp e
where e.deptno = d1.deptno
) as numTable
) salaries
, d1.dname
from dept d1
) d

I had expected to see each department name and the average salary within that department. Instead, I see the same Department_Avg_Salary value for each row - the average of the first department:

DNAME DEPARTMENT_AVG_SALARY
-------------- ---------------------
ACCOUNTING 1875
RESEARCH 1875
SALES 1875
OPERATIONS 1875

However, when I change the query to the following:

select d.dname
, d.salaries
from ( select cast
( multiset
( select e.sal
from emp e
where e.deptno = d1.deptno
) as numTable
) salaries
, d1.dname
from dept d1
) d

I get the following result - note that each department shows the correct list of salaries, not the list of the 1st department's salaries over and over.

DNAME
--------------
SALARIES
---------------------------------------------------------ACCOUNTING
NUMTABLE(2450, 1300)

RESEARCH
NUMTABLE(800, 2975, 3000, 5000, 1100, 3000)

SALES
NUMTABLE(1600, 1250, 1250, 2850, 1500, 950)

OPERATIONS
NUMTABLE()

Can someone explain why the
, ( select avg(column_value)
from table
( cast( d.salaries as numTable)
)
) Department_Avg_Salary

does not give an average per department but instead only the first department's average?

thanks for your help!

regards

Lucas Jellema