Selecting from colection within a table

I'm trying to see if I can use collections within tables instead of long where conditions with lots of ORs but I'm stumbling right now.

I have an existing table similar to below along with code used to select rows from the table:

TABLE old_source_table
(

   diag_i_1   VARCHAR2(10),
   diag_i_2   VARCHAR2(10),
   diag_i_3   VARCHAR2(10),

old code:

SELECT *
  FROM old_source_table

 WHERE diag_i_1 IN ( 'A','B','C','D','E' )
    OR diag_i_2 IN ( 'A','B','C','D','E' )
    OR diag_i_3 IN ( 'A','B','C','D','E' );

Using a collection within the table like below, I want to emulate selecting from the table when the column could contain a list of values like above.

TABLE test
(

   diagnosis_code t_diagnosis_code_va11, );

CREATE OR REPLACE TYPE t_diagnosis_code_va11 AS VARRAY(11) OF VARCHAR2(10);

INSERT INTO TEST VALUES ( t_diagnosis_code('A','B','C') ); INSERT INTO TEST VALUES ( t_diagnosis_code('A') );

SELECT *
  FROM test a
 WHERE 'A' IN ( SELECT * FROM TABLE(a.diagnosis_code) );

returns two rows.

HOW would I emulate selecting rows where the diagnosis_code could be a list of values such as 'A' or 'B'
like the IN statements in the original code?

When I try:

SELECT *
  FROM test a
 WHERE ('A','B') IN ( SELECT * FROM TABLE(a.diagnosis_code) );

I get: ORA-00947: not enough values

Any help would be appreciated.

Thanks Received on Sun Jan 25 2009 - 12:06:56 CST