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
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