SQL question: function output appended to WHERE clause

From: Sharkie <sharkdba_at_yahoo.com>
Date: 21 May 2004 11:37:00 -0700
Message-ID: <423b5ab1.0405211037.41048b2e@posting.google.com>

Hello,

I'm using Oracle 8.1.7 on a Solaris box. This might be a simple problem, not sure, but I really need this to work.

Supposedly I have a temp table:
SQL> desc temp
 Name Type

• ---------------- ID NUMBER(38) NAME VARCHAR2(100)

and some sample values:

SQL> select * from temp;

        ID NAME

---------- ----------------
         1 one
         2 second value
         3 three
         4 fourth
         5 five

...

I can easily select the rows I need manually through: select * from temp where id in (1,2,5);

Now, let's say I have a function which returns '1,2,5'.

SQL> select f_temp from dual;
1,2,5

Can I use this function to get the same results? These are not working:

select * from temp where id in (select f_temp_from dual);
select * from temp where id in f_temp;
select * from temp where id in (f_temp);

I get ORA-01722: invalid number, which I understand, since the returned value is a string, not a series of numbers. However having the function return a set of values as a string above, is it possible to use that in the query from temp table? Or any other solutions?

btw. if the above works if the function returns only one number. Received on Fri May 21 2004 - 13:37:00 CDT