Select from Execute function collection derived table??
I have a function F(varchar(100)) that returns a sequence of strings -
i.e., "Execute function F('a')" will result in a bunch of strings
being returned.
I can do the following:
create table mystrings (string varchar(100));
insert into mystrings execute function F('a'));
select * from mystrings where string like '%why%';
But what I'd really like to do is something like:
select * from TABLE(multiset(execute function F('a')) as vtab(x)
where ....
Is this possible in Informix, or does the expression inside the
TABLE() have to be a select statement?
Is there any other way to do a subselect from an Execute function
without creating an intermediate table?
Many thanks,
Mike
Mike DW wrote:
> I have a function F(varchar(100)) that returns a sequence of strings -
> i.e., "Execute function F('a')" will result in a bunch of strings
> being returned.
>
> I can do the following:
>
> create table mystrings (string varchar(100));
> insert into mystrings execute function F('a'));
> select * from mystrings where string like '%why%';>
> But what I'd really like to do is something like:
>
> select * from TABLE(multiset(execute function F('a')) as vtab(x)
> where ....>
> Is this possible in Informix, or does the expression inside the
> TABLE() have to be a select statement?
>
> Is there any other way to do a subselect from an Execute function
> without creating an intermediate table?
>
> Many thanks,
>
> Mike
--DROP FUNCTION f_test;
CREATE FUNCTION f_test() RETURNING VARCHAR(255);
RETURN 'A' WITH RESUME;
RETURN 'B' WITH RESUME;
RETURN 'C' WITH RESUME;
RETURN 'D' WITH RESUME;
RETURN 'E' WITH RESUME;
RETURN 'F' WITH RESUME;
RETURN 'G' WITH RESUME;
RETURN 'H' WITH RESUME;
END FUNCTION;
EXECUTE FUNCTION f_test();
SELECT *
FROM table(function f_test());
Done on 11.50, but it should work in 10.
Check the manual for the SELECT statement, FROM clause and then ITERATOR functions.
If this is not you want say so...
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Sep 16, 3:12 pm, Fernando Nunes <domusonl...@gmail.com> wrote:
> Mike DW wrote:
> > I have a function F(varchar(100)) that returns a sequence of strings -
> > i.e., "Execute function F('a')" will result in a bunch of strings
> > being returned.
>
> > I can do the following:
>
> > create table mystrings (string varchar(100));
> > insert into mystrings execute function F('a'));
> > select * from mystrings where string like '%why%';>
> > But what I'd really like to do is something like:
>
> > select * from TABLE(multiset(execute function F('a')) as vtab(x)
> > where ....>
> > Is this possible in Informix, or does the expression inside the
> > TABLE() have to be a select statement?
>
> > Is there any other way to do a subselect from an Execute function
> > without creating an intermediate table?
>
> > Many thanks,
>
> > Mike
>
> --DROP FUNCTION f_test;
> CREATE FUNCTION f_test() RETURNING VARCHAR(255);>
> RETURN 'A' WITH RESUME;
> RETURN 'B' WITH RESUME;
> RETURN 'C' WITH RESUME;
> RETURN 'D' WITH RESUME;
> RETURN 'E' WITH RESUME;
> RETURN 'F' WITH RESUME;
> RETURN 'G' WITH RESUME;
> RETURN 'H' WITH RESUME;
> END FUNCTION;
>
> EXECUTE FUNCTION f_test();>
> SELECT *
> FROM table(function f_test());>
> Done on 11.50, but it should work in 10.
> Check the manual for the SELECT statement, FROM clause and then ITERATOR functions.
> If this is not you want say so...
>
> Regards.
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
Fantastic! Thanks very much Fernando.
Mike