Selecting from colection within a table

From: Dereck L. Dietz <>
Date: Sun, 25 Jan 2009 13:06:56 -0500
Message-ID: <cR1fl.2907$>

Windows 2003 Server

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:

  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') );

  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:

  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

Original text of this message