Problem With SQL Query

[s_modem]

Hi,
I am querying a table by getting values from a function in the where caluse .
My Query
----------
Select * from emp where empid in (myFunction()

The function returns 1,2,3,4.But the query is not giving me the desired results.
Is there any way to get the results besides using DBMS_SQL.

Your help is greatly appreciated.

Thanks in Advance.
--SModem.

[stecal]

Well, if the query is returning something, but not the desired results, don't you think that perhaps your query is messed up? So, without knowing what the function does, it's hard to help you.

Plus, there are probably no less than 100 ways to do the query in lieu of the function.

[s_modem]

Hi,
Thanks for your help.
The function returns values 1,2,3,4.And I do have data in the Emp table. But when I am executing the query it shows no records.
I guess it is taking the function result as one single value instead of multiple values. Is there any work around for this ?

Thanks,
--SModem.

[stecal]

Originally posted by stecal
So, without knowing what the function does, it's hard to help you.

The code?

[s_modem]

Hi,
The function returns a comma separated value.
Here is the Eg.
Name of the function : get_data('Name')

select get_data('Name') from dual;
---
1,2,3,4

Select id from emp where id in (1,2,3,4)
----
1
2
3
4

Select id from emp where id in (get_data('Name'));
--
No Data found

But I am expecting a result of
--
1
2
3
4

Thanks In Advance.
--SModem.

[stecal]

The purpose of a function is to take zero or more parameters and return a single value. You are returning a string. How is ID, a number, supposed to be compared to a string value of '1,2,3,4'?

http://tahiti.oracle.com/pls/db92/db...?section=42070