Referencing the object generated by the CAST-MULTISET construct in 8i

Hi,

   I'm hoping that someone can help me with a problem I have with using the 8i CAST-MULTISET outlined below. We are trying to 'flatten' a result set so that instead of returning results such as :-

       USER_NAME         FLAG
       =========         ====



       'Kev Grindley'      'ABC'

       'Kev Grindley'      'DEF'


       'Kev Grindley'      'GHI'

...we want the following :-

       USER_NAME         FLAG
       =========         ====



       'Kev Grindley'    'ABC','DEF','GHI'

This can be achieved by using the CAST-MULTISET construct similar to the following :-

       SELECT p.col1, p.col2,
         CAST(MULTISET(SELECT c.col1
                       FROM   child_table c
                       WHERE  c.key = p.key) AS child_dets_table ) AS
child_nt
       FROM parent_table p
       WHERE p.key = 'SOMEVALUE'

The table child_Dets_table is defined as :

CREATE OR REPLACE TYPE child_dets_table AS TABLE OF child_dets;

...where child_dets is :

CREATE OR REPLACE TYPE child_dets AS OBJECT (

      col1 VARCHAR2 ...);

This works fine but what we really want is the 'outer'(parent_table) SELECT statement to return nothing when the 'inner' CAST-MULTISET SELECT fails to return any rows. So what we really want is to add a condition to the outer WHERE clause to evaluate something like '...AND child_nt IS NOT NULL'

The problem is I can find no documentation illustrating how to reference the properties of the child_nt object from within the WHERE clause - the outer one in this case.

After trawling through what documentation we have, I have tried to intuit what the syntax may be with no success. Any help gratefully received. (We're using Oracle 8.1.7.0.0 on an IBM NUMA box SV R4 UNIX). Thanks,
Kev Grindley. Received on Mon May 21 2001 - 17:34:51 CEST