--I have the following:

CREATE TYPE nj_analyst.contacts_in_month_rec IS OBJECT
  (idn_participants NUMBER
  ,dte_entered DATE);
/

CREATE OR REPLACE TYPE nj_analyst.contacts_in_month_tab IS VARRAY(31) OF nj_analyst.contacts_in_month_rec;
/

--and I have a table children with these collections:
,contacts_this_month nj_analyst.contacts_in_month_tab
,contacts_last_month nj_analyst.contacts_in_month_tab

--which have been populated by
                    (SELECT CAST(MULTISET(
                        SELECT MAX(idn_participants) KEEP (DENSE_RANK LAST ORDER BY dte_endered, idn_participants)
                              ,MAX(TRUNC(dte_held)) KEEP (DENSE_RANK LAST ORDER BY dte_endered, idn_participants)
                        FROM nj_data.all_contact_info a
                        WHERE a.case_number = x.case_num
                        AND a.smem_member = x.member_num)
                        AS contacts_in_month_tab)
                      FROM DUAL
                      )

How would I compare the two collections to see if any of the dates entered (dte_entered)are in both contacts_THIS_month and contacts_LAST_month.

any help is appreciated


Scott Kirner