using a collection returned from a function in a where clause
All,
I am trying to create a fuzzy find type of SPL function that returns a set
of possible matches from several tables. At the dbaccess command prompt, it
appears to be returning the set correctly, but I cannot then use it in a
select statement
--quicky function to create and populate a set
create function get_set()
returning set(integer not null);
define s set(integer not null);
insert into table(s)
values(1);
insert into table(s)
values(2);
return s;
end function;
At the command prompt in dbaccess or isql:
execute function get_set();
gives the following:
(expression) SET{1 ,2 }
Now the following works where I have a table with an integer key, and I
have a query:
select somevalue
from sometable
where somekey in (1,2);
But...if I try to use it as
select somevalue
from sometable
where somekey in get_set();
I get error -1260: It is not possible to convert between the specified
types.
Among other things I have tried to cast the return from
get_set()::set(integer not null) and tried somekey::integer.
No luck. Any ideas...
many thanks,
Andrew.