Hi
Create or replace type
address_record
as object
( address_no number,
address_name varchar2,
address_startDate date,
address_expiryDate date
);
create or replace type
address_rec_tab
as table of address_record;
Create table
employee
(emp_no number,
emp_name varchar2,
adresses address_rec_tab
);
1st approach
==========
<pre>
select
emp.emp_no,
emp.emp_name,
emp.addresses
from
employee emp,
table(*emp.addresses*) add
where add.address_expiryDate >=sysdate
</pre>
In the above example my SQL query address collection object is not returning filtered or current address list.
I suppose this is due to fact taht my where clause is not attached to the nested table.
Through my reading I gather that I can only use the following query to filter the address collection.
2nd approach
==========
<pre>
select
emp.emp_no,
emp.emp_name
cursor(select address_no,
address_name,
address_startDate,
address_expiryDate
from
employee emp,
table (*emp.addresses*) add
where add.address_expiry_date >=sysdate)
from
employee emp,
table (*emp.addresses*) add
where add.address_expiry_date >=sysdate) -- probably this redundent
</pre>
But this approch forces me to rebuild addresses collection object.
I was wondering anybody can suggest me a way so that 1st approach works? I do not have to rebuild collection object in this way.
Thanks for your help in advance
Regards
Charan