| select statement ... [message #372073] |
Tue, 09 January 2001 02:14  |
jimmy
Messages: 21
Registered: November 2000
|
Junior Member |
|
|
Hi,
I have a select statement that looks like this;
select * from myTable where aColumn in (1, 3, 4);
This will return all records with the value 1, 3 and 4 in column aColumn.
My question;
Is it possible to write a pl/sql function that returns a list of numbers to the statement? Like this;
select * from myTable where aColumn in (getNumbers);
The function getNumbers should for exmaple return 1, 3, 4.
Thank you!
Jimmy
|
|
|
|
|
|
| Re: select statement ... [message #372081 is a reply to message #372073] |
Tue, 09 January 2001 08:38  |
jimmy
Messages: 21
Registered: November 2000
|
Junior Member |
|
|
Hi,
I know that "select * from myTable where aColumn in (select number from dual);" works.
I also know that "select * from myTable where aColumn in (getNumbers);" works if it returns one value.
The problem is if I want several values returned from a pl/sql function, not from a subquery.
//Jimmy
|
|
|
|
| Re: select statement - PL/SQL loop... [message #372087 is a reply to message #372073] |
Tue, 09 January 2001 13:10 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
|
if the numbers are queried from a table at some point, you can open a cursor in a function/proc, loop through the results and concatenated them together with commas in between. Problem is that there will be a limit to the string length and this is not much different to a subquery anyway.
|
|
|
|
| Re: select statement ... [message #372100 is a reply to message #372073] |
Wed, 10 January 2001 05:30 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Sadly there is no way to return a list from Pl/Sql to an IN statement. (Or if there is, I don't know of it and would dearly like to be told)
What you can do is to create a function into which you pass the value from the database, which will then do a comparison between that value and the list, returnin a 'Y' if a match is found and a 'N' otherwise.
This will let you rewrite the query as
Select *
From MyTable
Where fn_in_list(aColumn) = 'Y'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
