troubleshooting Question

Oracle select column that is number and check in list of varchar for matches.

Avatar of MarioC82
MarioC82Flag for Cyprus asked on
Oracle Database
11 Comments1 Solution609 ViewsLast Modified:
Hi,

I have a function that returns a comma deliminated list of numbers in single quotes. If i select myfunction() from dual i get the following:

'1','2','3','4'

What i need to do is select code which is a number from a table where the code value is in the result list myfunction() returns. Of course i get the error Invalid number. I have tried casting it the code in the select but then i find no results.

Example:

1) What i do and i get invalid number exception, because code is numeric and myfunction() return a string '1','2','3','4'
select * from mytable where code in (select myfunction() from dual);

2) What i tried to overcome it, no exception but no results show:
select * from mytable where to_char(code) in (select myfunction() from dual);

Is there some way to make this select work?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros