Re: PL/SQL Help! Trying to Use Variable String as Argument in "where in" clause

In your case you can try something like this :

select 'x' from my_table
where 1=decode(sign(instr(my_field,v_string)),1,0);

Be careful , if length of my_field more then 1 char, this may not work properly.
For example if my_field='A' and v_string='AB,BD,B' select above will return 1 record.

Slava.

James Lorenzen wrote:

> In article <3560ec9b.112905855_at_news.mindspring.com>,
> theslays_at_mindspring.com wrote:
>
> >Please email any responses to als_at_akc.org
> >
> >Thank you in advance for your help
> >
> >I'm trying to use a variable defined as a string of values in a SQL
> >select statement. So far, defining a variable as a string of values
> >works when I run the SQL but doesn't select anything when I try to
> >execute the same statement as part of a PL/SQL procedure's cursor.
> >Any tips?
> >
> >Example:
> >IF some_condition = 1 then
> > v_string := '''A'',''B'',''C''';
> >ELSIF some_condition = 2 then
> > v_string := '''D'',''E'',''F''';
> >ELSE v_string := '''G'',''H'',''I''';
> >END IF;
> >
> >Select 'x'
> >from my_table
> >where my_field in (v_string);
> I have the same needs and my solution was to use dynamic sql (the DBMS_SQL
> package). It is a little more work, but it solves my needs. Please note
> that the "IN" part of the WHERE clause works on "sets" of infomation. A
> variable is is not a set. If anyone has the magic work around to get a
> variable recognized as a set, I am very interested.
>
> HTH
> James
> --
> lorenzen_at_tiny.net | Life is complex; it has
> | real and imaginary parts
Received on Mon May 25 1998 - 19:57:56 CEST