?
Solved

Select where in problem

Posted on 2010-01-12
11
Medium Priority
?
371 Views
Last Modified: 2012-05-08
Hi experts!

I have a problem with the following query. This one is made of a principal query and a subquery. As designed so far, the subquery returns a  list of ID's seperated by a comma (,) so it's results looks like "1,2,3,4,5,6,7" and this part works.

Now I try to include this "string" of results into a select where in statement so it would look like select * from blabla where xxx in (1,2,3,4,5,6,7) but Oracle triggers an error ORA-01722: invalid number, so I guess something must be wrong with the content of the result of my subquery, probably the datatype or my query is faulty by design.

Do you guys know any solution ?

select BG_USER_07 "Subject",
BG_SUMMARY "Fonction",
BG_USER_14 "Qualification",
BG_SEVERITY "Severity",
BG_BUG_ID "ID",
BG_STATUS "Status"
from bug_db.bug
where BG_BUG_ID IN (
SELECT TO_CHAR(REPLACE(LOWER(SUBSTR(CSET_VALUE, INSTR(CSET_VALUE,'LogicalFilter')+24, (INSTR(CSET_VALUE,'VisualFilter')-INSTR(CSET_VALUE,'LogicalFilter')-27))), ' or ', ','))
FROM bug_db.COMMON_SETTINGS
WHERE CSET_CATEGORY = 'BugViews'
and CSET_NAME = '1.3.5.0'
)

Thanks for the help !
0
Comment
Question by:desmeulesj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26292757
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:
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26292809
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 ', ','));
0
 
LVL 4

Expert Comment

by:Audhi203
ID: 26292819
Probably you have to put escape sequence for comma i.e. it would be '','' add additional single quote.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26292829
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'

Open in new window

0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26293016
There is a chance that each row retrieves comma separated values
0
 

Author Comment

by:desmeulesj
ID: 26293181
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
0
 

Author Comment

by:desmeulesj
ID: 26293201
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
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 26293364
Try this.

It's ugly and might have some syntax problems since I can't duplicate your tables.

In a nutshell, I take your comma separated list, convert it to XML and extract the values as individual rows.  You can then use these individual rows in the 'in list' to the outer select.
select BG_USER_07 "Subject",
BG_SUMMARY "Fonction",
BG_USER_14 "Qualification",
BG_SEVERITY "Severity",
BG_BUG_ID "ID",
BG_STATUS "Status"
from bug_db.bug
where BG_BUG_ID IN (
select extractvalue(column_value,'x/text()')
from bug_db.COMMON_SETTINGS t1, 
   TABLE(XMLSEQUENCE(EXTRACT(xmltype('<root><x>' || replace(
   (
		SELECT TO_CHAR(REPLACE(LOWER(SUBSTR(CSET_VALUE, INSTR(CSET_VALUE,'LogicalFilter')+24, (INSTR(CSET_VALUE,'VisualFilter')-INSTR(CSET_VALUE,'LogicalFilter')-27))), ' or ', ','))
		FROM bug_db.COMMON_SETTINGS
		WHERE CSET_CATEGORY = 'BugViews'
		and CSET_NAME = '1.3.5.0'
   )
   ,',','</x><x>') || '</x></root>'), '/root/*')))
)
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26293389
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/*')))
)
/

Open in new window

0
 

Author Closing Comment

by:desmeulesj
ID: 31676062
Hehe works like a charm, the query is a bit slow tho :)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26293832
>>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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question