Avatar of MarioC82
MarioC82
Flag for Cyprus asked on

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

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?
Oracle Database

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
slightwv (䄆 Netminder)

You need dynamic sql or need to rewrite myfunction to return something like an associative array.

Dynamic sql would go like:

declare
   mysql varchar2(500);
   mycursor sys_refcursor;
begin
   mysql := 'select * from mytable where code in (' || myfunction() || ')';
   open mycursor for mysql;
...
end;
/
Gerwin Jansen

You can try putting in a 'replace' like this:

select * from mytable where code in (replace(select myfunction() from dual,'''',''));
MarioC82

ASKER
@gerwinjansen, i tried to use replace but it says missing expression, i have checked it on the oracle documentation of the replace function and it seems the usage is ok.

select * from mytable where code in (replace(select myfunction() from dual), '''','');
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gerwin Jansen

Ok , I tried with the string you supplied instead of the function (which I don't have), you could try like this instead:

select * from mytable where code in (replace(myfunction(),'''',''));
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MarioC82

ASKER
Thanks alot, the issue was solved.
Gerwin Jansen

>> YOU CANNOT DO THIS
Care to explain? Is it the datatype VARCHAR2 of the function that you used?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>Care to explain?

Try it.  It will error out.

>>Is it the datatype VARCHAR2 of the function that you used?

Yes.

Using IN in the select requires a LIST of some type.  The return value of the function is a varcahr2, not a list.

Check the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions013.htm#i1050801

IN takes either an expression_list or a sub select.

Expression_list is defined here:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions015.htm#i1033664
Gerwin Jansen

Ok, so if the function would return a list type then it would work, I thought so. Thx.
slightwv (䄆 Netminder)

>>if the function would return a list type then it would work

Yes.  That is what I posted in my first post:  http:#a38148678  "rewrite myfunction to return something like an associative array"
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gerwin Jansen

:D lol - enough on this one...