Re: LIKE with varying number of values

From: joel garry <joel-garry_at_home.com>
Date: Mon, 20 Jul 2009 10:31:32 -0700 (PDT)
Message-ID: <a8a23b47-6149-4208-b751-e8aadcead268_at_i8g2000pro.googlegroups.com>

On Jul 19, 12:08 pm, Dereck <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0
> Windows 2003 Server
>
> I have a cursor defined as follows which I can use in a number of
> different procedures:
>
>     CURSOR cr_cursor( p_start_date IN DATE,
>                       p_end_date   IN DATE,
>                       p_tab        IN t_cpt_hcpcs_tab,
>                       p_service    IN tmp$_measures.service_type_code
> %TYPE )
>         RETURN tmp$_measures$ROWTYPE
>     IS
>       SELECT C_ADMIN_DATA,
>              NULL,
>              NULL,
>              SUBSTR(mbr_key,1,27),
>              mbr_sys_key,
>              person_id,
>              TRUNC(srv_dt_from),
>              p_service
>         FROM v_claim
>        WHERE srv_dt_from BETWEEN p_start_date AND p_end_date
>          AND person_id > 0
>          AND cpt_hcpcs IN ( SELECT column_value FROM TABLE(p_tab) )
>       UNION ALL
>         SELECT C_STATE_HISTORICAL_CLAIM,
>                NULL,
>                NULL,
>                SUBSTR(mbr_key,1,27),
>                mbr_sys_key,
>                person_id,
>                TRUNC(srv_dt_from),
>                p_service
>           FROM v_claim_external
>          WHERE srv_dt_from BETWEEN p_start_date AND p_end_date
>            AND person_id > 0
>            AND cpt_hcpcs IN ( SELECT column_value FROM TABLE(p_tab) );
>
> I would like to figure out a way where I could convert the cursor
> below to where I can
> reuse it from different procedures using a varying number of procedure
> codes similar to
> how the above cursor works.  The only problem is that if I try the
> same syntax as above
> I get a too many values retrieved error from using the LIKE.
>
>       CURSOR cr_cursor( p_start_date IN DATE,
>                         p_end_date   IN DATE )
>           RETURN tmp$_measures%ROWTYPE
>       IS
>         SELECT C_ADMIN_DATA,
>                NULL,
>                NULL,
>                SUBSTR(mbr_key,1,27),
>                mbr_sys_key,
>                person_id,
>                TRUNC(srv_dt_from),
>                C_DIPTHERIA
>           FROM v_claim
>          WHERE srv_dt_from BETWEEN p_start_date AND p_end_date
>            AND person_id > 0
>            AND (
>                     proc_i_1 LIKE '9936%'
>                  OR proc_i_1 LIKE '9939%'
>                 )
>         UNION ALL
>           SELECT C_STATE_HISTORICAL_CLAIM,
>                  NULL,
>                  NULL,
>                  SUBSTR(mbr_key,1,27),
>                  mbr_sys_key,
>                  person_id,
>                  TRUNC(srv_dt_from),
>                  C_DIPTHERIA
>             FROM v_claim_external
>            WHERE srv_dt_from BETWEEN p_start_date AND p_end_date
>              AND person_id > 0
>              AND (
>                       proc_i_1 LIKE '9936%'
>                    OR proc_i_1 LIKE '9939%'
>                   );
>
> IS there a way where I can use the LIKE clause with a varying number
> of values?
>
> Thanks

search for varying in-list on asktom.oracle.com

You have to be careful of getting too generalized, as you can create a parsing performance problem if you do it wrong, particularly if you scale up. You want cursors to be reusable and shareable. Some details vary by version, too, you don't want to create a maintenance issue by being overly cute.

jg

--
_at_home.com is bogus.
http://news.bbc.co.uk/2/hi/technology/8132547.stm

Received on Mon Jul 20 2009 - 12:31:32 CDT