Link to home
Start Free TrialLog in
Avatar of MarioC82
MarioC82Flag 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?
Avatar of slightwv (䄆 Netminder)
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;
/
You can try putting in a 'replace' like this:

select * from mytable where code in (replace(select myfunction() from dual,'''',''));
Avatar of 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), '''','');
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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks alot, the issue was solved.
>> YOU CANNOT DO THIS
Care to explain? Is it the datatype VARCHAR2 of the function that you used?
>>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
Ok, so if the function would return a list type then it would work, I thought so. Thx.
>>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"
:D lol - enough on this one...