Re: LIKE with varying number of values

From: joel garry <joel-garry_at_home.com>
Date: Wed, 29 Jul 2009 16:06:04 -0700 (PDT)
Message-ID: <d7cc0c1a-4a2f-4b71-bba0-a6f1266bd110_at_x25g2000prf.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

http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html also has an interesting trick in it.

jg

--
_at_home.com is bogus.
Drunken online ranter on his way to jail:
http://www3.signonsandiego.com/stories/2009/jul/29/la-mesan-convicted-obama-threats/?uniontrib

Received on Wed Jul 29 2009 - 18:06:04 CDT