Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Select where in problem

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
desmeulesj
Asked:
desmeulesj
  • 5
  • 3
  • 2
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
Audhi203Commented:
Probably you have to put escape sequence for comma i.e. it would be '','' add additional single quote.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
slightwv (䄆 Netminder) Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
There is a chance that each row retrieves comma separated values
0
 
desmeulesjAuthor Commented:
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
 
desmeulesjAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
desmeulesjAuthor Commented:
Hehe works like a charm, the query is a bit slow tho :)
0
 
slightwv (䄆 Netminder) Commented:
>>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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now