Select where in problem

In a nutshell: You can't do this like this. To use a comma separated list this way you need to use dynamic SQL and PL/SQL.

That said, just remove the code that creates the comma-separated list and return the list itself.

To help provide a working example what does the following return:

Can u give one or two sample  output rows for the query
SELECT TO_CHAR(REPLACE(LOWER(SUBSTR(CSET_VALUE, INSTR(CSET_VALUE,'LogicalFilter')+24, (INSTR(CSET_VALUE,'VisualFilter')-INSTR(CSET_VALUE,'LogicalFilter')-27))), ' or ', ','));

Probably you have to put escape sequence for comma i.e. it would be '','' add additional single quote.

I just realized my code didn't post.  I was after something similar.

I would like to see the output from:

SELECT CSET_VALUE
FROM bug_db.COMMON_SETTINGS
WHERE CSET_CATEGORY = 'BugViews' 
and CSET_NAME = '1.3.5.0'

Select allOpen in new window

There is a chance that each row retrieves comma separated values

slightwv, here is what the query you requested returns:

{
grid: \000000C1\1,0;51,BG_BUG_ID;68,BG_SEVERITY;66,BG_STATUS;70,BG_USER_14;70,BG_RESPONSIBLE;70,BG_DETECTION_DATE;70,BG_DETECTED_BY;191,BG_SUMMARY;206,BG_USER_13;154,BG_USER_07;133,BG_TARGET_REL;70,BG_USER_01;,
filter: \0000032C\[Filter]{
TableName:BUG,
ColumnName:BG_BUG_ID,
LogicalFilter:\0000015c\8167 OR 8231 OR 8368 OR 8431 OR 8501 OR 8491 OR 8530 OR 8577 OR 7929 OR 8048 OR 8118 OR 8121 OR 8357 OR 7096 OR 8212 OR 4932 OR 8367 OR 8146 OR 8329 OR 8328 OR 8342 or 8148 or 8487 or 8218 or 8342 or 8148 or 8716 or 7096 or 8628 or 8639 or 8750 or 8752 or 8342 or 8717 or 8696 or 8625 or 8148 or 8764 or 8593 or 8741 or 8644 or 8740 or 8593 or 8676,
VisualFilter:\0000015c\8167 OR 8231 OR 8368 OR 8431 OR 8501 OR 8491 OR 8530 OR 8577 OR 7929 OR 8048 OR 8118 OR 8121 OR 8357 OR 7096 OR 8212 OR 4932 OR 8367 OR 8146 OR 8329 OR 8328 OR 8342 or 8148 or 8487 or 8218 or 8342 or 8148 or 8716 or 7096 or 8628 or 8639 or 8750 or 8752 or 8342 or 8717 or 8696 or 8625 or 8148 or 8764 or 8593 or 8741 or 8644 or 8740 or 8593 or 8676,
NO_CASE:
}
,
show_rdcomment: "y",
show_description: "y",
show_summary: "y",
show_dropdown: "y"
}

The subquery I designed essentially strips everything but the list of id's you can see if the result above

And for schajukg, here are the current results of the subquery:
8167,8231,8368,8431,8501,8491,8530,8577,7929,8048,8118,8121,8357,7096,8212,4932,8367,8146,8329,8328,8342,8148,8487,8218,8342,8148,8716,7096,8628,8639,8750,8752,8342,8717,8696,8625,8148,8764,8593,8741,8644,8740,8593,8676

Here's a simplified test case that shows the concept.

drop table tab1 purge;
create table tab1(col1 varchar2(500));

drop table tab2 purge;
create table tab2(col1 varchar2(10));

insert into tab1 values('8167,8231,8368,8431,8501,8491,8530,8577,7929,8048,8118,8121,8357,7096,8212,4932,8367,8146,8329,8328,8342,8148,8487,8218,8342,8148,8716,7096,8628,8639,8750,8752,8342,8717,8696,8625,8148,8764,8593,8741,8644,8740,8593,8676');


insert into tab2 values('8167');
insert into tab2 values('8676');
insert into tab2 values('9999');
commit;

select col1 from tab2
where col1 in (
	select extractvalue(column_value,'x/text()')
	from tab1 t1, 
   	TABLE(XMLSEQUENCE(EXTRACT(xmltype('<root><x>' || replace(col1,',','</x><x>') || '</x></root>'), '/root/*')))
)
/

Select allOpen in new window

Hehe works like a charm, the query is a bit slow tho :)

>>the query is a bit slow tho

Glad it worked.  Now that you know the basic concept you might be able to tweak it a little to eliminate some steps.  It's doing a lot of work to massage the data.

I'm sure you can replace some of the replaces to get to the xml a little quicker.