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

From: Neil W. James <Family.James_at_wanadoo.fr>
Date: Sun, 24 May 1998 21:21:01 +0100
Message-ID: <ant242001868BSvY_at_wanadoo.fr>


[Quoted] In article <3560ec9b.112905855_at_news.mindspring.com>, Alan Slay <URL:mailto: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);
>

You don't HAVE to use dynamic SQL as suggested by others, though it is probably clearer.

In our application at work, we used to use dynamic SQL as suggested, but when we moved to Oracle 7 and all the SQL was kept for reuse, we needed an alternative. One of the DBAs came up with the following method.

We had a string variable containing text of the form (1, 2, 3) where 1, 2 and 3 were (partial) keys of another table, and it already had the brackets. When we looked up that table to get out other attributes, we wanted to have

select ...
 where key in :string

The solution was to use TRANSLATE and INSTR

select ...
 where instr(translate(string, '() ', ',,'),

             ','||to_char(key)||',') > 0  

That is, make the string into a standardised comma-separated string (note that we had to convert the brackets to get the leading and terminating separators and get rid of the blanks) and the target into the same format and see if you get a match, eg test if the string ,1, is in ,1,2,3,

If my_field is a string that can contain commas or spaces, then obviously it'll need changing a bit.

HTH

-- 
Neil James and Family
Neil.James_at_wanadoo.fr
Received on Sun May 24 1998 - 22:21:01 CEST

Original text of this message