I have SQL in this form:

SELECT * FROM MyTable WHERE ID IN (1,2,3,4)

In the place of 1,2,3,4 I would prefer:

SELECT * FROM MyTable WHERE ID IN ( MyFunction('SomeValue')  )

--

If "MyFunction" returns a single INTEGER the above works. If it returns a VARCHAR or CLOB I just get an error.

I believe the SELECT statement is expecting an INTEGER or an ARRAY of INT ...

But I don't think I can return an array from a function, any way around this?

Adam,

> In the place of 1,2,3,4 I would prefer:
>
> SELECT * FROM MyTable WHERE ID IN ( MyFunction('SomeValue')  )

In the case your function returns a string and you are using a script or
stored prodedure, you could combine the statement like this:

SCRIPT
BEGIN

  DECLARE Result CURSOR WITH RETURN FOR Stmt;
  PREPARE Stmt FROM
     'SELECT * FROM Adressen WHERE ID IN (' + MyFunction() + ')';
  OPEN Result;

END

CREATE FUNCTION "MyFunction" ()
RETURNS VARCHAR(200) COLLATE DEU_CI
BEGIN

RETURN '1,2,3,4';

END

Uli

Adam


Apart from Uli's suggestion the only thing that comes to mind to try is to write you function to return a cursor. Don't know if it would work (guessing because sub selects do) or what the effect on memory leaks would be.

Roy Lambert [Team Elevate]

Thanks for these suggestions Uli & Roy,

Uli's would definitely work, but I am trying to only work in the world of SQL SELECT, not SCRIPT, as these statements would be run by my users and I control access to my DB to prevent them from running SCRIPTs or SQL other than simple SELECTs.

I guess unless the ability to return an Array from a function is added to EDB SQL I will have to figure out a work-around as suggested by Uli.

Turned my head around 180 degrees and fixed it (not especially elegant!)

SELECT * FROM MyTable WHERE  MyFunction('SomeValue')  LIKE '%'+CAST(ID as VARCHAR(20))+'%'

Adam,

t has to be a list of values of the same type of ID, not a string or any other thing.
Instead of a fixed list or a function as you were trying you can replace it with a SELECT statement that returns the list you want. For example:

SELECT * FROM Invoices WHERE Customer_ID IN (SELECT Customer_ID FROM Customers WHERE AmountDue > 10000)

--
Fernando Dias
[Team Elevate]