returning an average from varray

g_currier
g_currier used Ask the Experts™
on
Hi,
 I am trying to return records with a query.  My problem is that part of what I am querying is a varray.  I don't have much experience with varrays except that I know how to feed them data to store.
Here is what I have:
 
create or replace type eval_va as varray(10) of dec(2,1);
/

create or replace type pers_ty as object
(
  fname varchar2 (25), 
  lname varchar2 (25),
  dob date,
  evals eval_va,
  member function pers_dob_fu(dob in date) return INTEGER
);
/

create or replace type body pers_ty as
  member function pers_dob_fu(dob date) return integer is
  begin
    return abs(to_date( to_char(sysdate,'dd-MM'),'dd-MM') - to_date( to_char(dob,'dd-MM'),'dd-MM'));
  end;
end;
/

create table instructors 
(
  emplid integer primary key,
  pers pers_ty
);

--DML
--Insert into "instructors"

insert into instructors values ('2', pers_ty('Fred','Flintstone','02-jan-1940',eval_va('5.0','3.4','4.3','2.5','1.2')));
insert into instructors values ('3', pers_ty('Homer','Simpson','03-jan-1968',eval_va('2.5','4.0','4.3','2.5','4.1')));
insert into instructors values ('4', pers_ty('Elmer','Fudd','01-feb-1976',eval_va('4.3','1.0','4.2','1.3','4.4')));
insert into instructors values ('5', pers_ty('Bugs','Bunny','02-feb-1945',eval_va('2.5','3.5','4.3','3.5','2.2')));
commit;


--Queries
select 
  A.emplid, 
  A.pers.fname||' '|| A.pers.lname Instructor,
  365 -  A.pers.pers_dob_fu(A.pers.dob) Days_to_next_birthday,
  B.*
from instructors A, table(A.pers.evals) B;

Open in new window


And here is a sample of the output from the query:
 
EMPLID                 INSTRUCTOR                                          DAYS_TO_NEXT_BIRTHDAY  COLUMN_VALUE           
---------------------- --------------------------------------------------- ---------------------- ---------------------- 
2                      Fred Flintstone                                     78                     5                      
2                      Fred Flintstone                                     78                     3.4                    
2                      Fred Flintstone                                     78                     4.3                    
2                      Fred Flintstone                                     78                     2.5                    
2                      Fred Flintstone                                     78                     1.2                    
3                      Homer Simpson                                       79                     2.5                    
3                      Homer Simpson                                       79                     4                      
3                      Homer Simpson                                       79                     4.3                    
3                      Homer Simpson                                       79                     2.5                    
3                      Homer Simpson                                       79                     4.1                    
4                      Elmer Fudd                                          108                    4.3                    
4                      Elmer Fudd                                          108                    1                      
4                      Elmer Fudd                                          108                    4.2                    
4                      Elmer Fudd                                          108                    1.3                    
4                      Elmer Fudd                                          108                    4.4                    
5                      Bugs Bunny                                          109                    2.5                    
5                      Bugs Bunny                                          109                    3.5                    
5                      Bugs Bunny                                          109                    4.3                    
5                      Bugs Bunny                                          109                    3.5                    
5                      Bugs Bunny                                          109                    2.2

Open in new window


My question is this:

How do I return the values stored (per id number) as an average of eval values so that each record occupies ONE row of info (instead of 5)?

I appreciate any help,

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
For clarification, I thought that this might work:


select
  A.emplid,
  A.pers.fname||' '|| A.pers.lname Instructor,
  365 -  a.pers.pers_dob_fu(a.pers.dob) days_to_next_birthday,
  (select avg(B.*/5) as eval_avg_score from table(a.pers.evals) b)
from instructors a;


but it didn't and it is obvious I do not undertstand varrays as well as I need to.

Commented:
you mean something like:

SQL> ed
Wrote file afiedt.buf

  1  select EMPLID,A.pers.fname||' '|| A.pers.lname Instructor,
  2  365 -  A.pers.pers_dob_fu(A.pers.dob) Days_to_next_birthday,
  3    avg(B.COLUMN_VALUE) avg_value
  4  from instructors A, table(A.pers.evals) B
  5* group by EMPLID,A.pers.fname||' '|| A.pers.lname, 365 -  A.pers.pers_dob_fu(A.pers.dob)
SQL> 
SQL> /

    EMPLID INSTRUCTOR                                                                                                                                                DAYS_TO_NEXT_BIRTHDAY  AVG_VALUE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------
         2 Fred Flintstone                                                                                                                                                              77       3.28
         3 Homer Simpson                                                                                                                                                                78       3.48
         4 Elmer Fudd                                                                                                                                                                  107       3.04
         5 Bugs Bunny                                                                                                                                                                  108        3.2

Open in new window

Author

Commented:
yes...It didn't occur to me to use "column_value" but that was it.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial