Re: LIKE with varying number of values

> Oracle 10.2.0.3.0
> Windows 2003 Server
>

[...]
>
> 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

Hello Dereck,
maybe REGEXP_LIKE is an option for you.

   proc_i_1 like '9936%' or proc_i_1 like '9939%'

can be rewritten as

   regexp_like(proc_i_1,'^993[69].*')

So if you can formulate your condition with a regular expression, regexp_like is your friend.

Hope that helps,
Lothar  

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |