Re: WHERE IN (v_SomeVariable)

(this may not be the best way, but this is how i've done it)

Are you using dynamic SQL or is this a standard PL/SQL cursor.

If you are using a standard PL/SQL cursor then your syntax won't work because the SQL processor will look at it like this

select stuff
  from table
 where expression in ('val1, val2, val3')

instead of how you expect it to work

select stuff
  from table
 where expression in ('val1','val2','val3')

When I had to use IN with standard PL/SQL cursors I created a function called IN_LIST which took two params (looking_for_value, list_of_values) and returned the value you were looking for if "looking_for_value" was in the comma seperated string of list_of_values.. (ie in_list ('A','A,B,C'))

the sql would look like

select stuff
  from table
 where in_list(expression, list_variable) = expression

The other way to do it is with dynamic SQL.. if v_variable contains strings you need to do this: sql := 'select stuff from table where expression in (''' \   + replace(v_varible, ',',''',''') + ''');';

otherwise you can just do
sql := 'select stuff from table where expression in \   (' + v_variable + ');'

If you need the code for in_list I can post it...

Hope that helps..

In article <ac100v$nje$1_at_news.gate.net>, nospam_at_nospam.spm says...
> Hi all,
>
> I'm helping someone with a PL/SQL script and I'm a little rusty. I'm
> trying to create a dynamic cursor that uses the WHERE IN () clause with a
> variable which is a varchar2 that is a comma delimited list of single quoted
> strings - ie
>
> '00002020','00004808','00002962'
>
> And the SQL/Cursor looks like this
>
> INSERT INTO SOMETABLE
> SELECT key_date, fdate
> FROM DATES
> WHERE dat.key_date IN (v_SomeVar);
>
> but this isn't returning any results. What is the best way to create a
> dynamic SQL statement using the WHILE clause with an IN modifier?
>
> Thanks,
> Tom
>
>
>
Received on Sat May 18 2002 - 22:28:41 CDT