Re: Selecting from colection within a table

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Mon, 26 Jan 2009 04:03:56 -0800 (PST)
Message-ID: <ff87611a-1baf-46bb-93ae-984f571f458e_at_n33g2000pri.googlegroups.com>

On Jan 25, 9:06 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.4.0
> 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:
>
> 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

CREATE OR REPLACE TYPE t_diag_code_ntab AS TABLE OF VARCHAR2(10);

SELECT diagnosis_code
  FROM test a
 WHERE t_diag_code_ntab('A','B') SUBMULTISET OF CAST(diagnosis_code AS t_diag_code_ntab)

Multiset operators and conditions do not work on VARRAYs, only on nested tables, thus the nested table type and CAST() in the query.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Jan 26 2009 - 06:03:56 CST