Handling multi-valued function output
Jeff had a UDR that returns multiple rows/columns via RETURN ... WITH RESUME. SELECT * FROM TABLE(my_function(param)) worked on IDS 11 but failed on IDS 10 with error -684 ("function returns too many values"), and he wanted to use it like a virtual table rather than looping with FOREACH. Art Kagel suggested casting to a MULTISET, but that didn't work; Fernando Nunes found in the SQL syntax manual the correct form: SELECT * FROM TABLE(FUNCTION my_function(param)). Another poster confirmed it on 10.00.UC4, including multi-column returns, using an alias list, e.g. ... AS t(col1,col2).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a complex UDR that uses RETURN x, y WITH RESUME to
generate a list of values.
I have found that with IDS 11, I can do
SELECT * from TABLE(my_function(param));
but with IDS 10, the same statement gets "-684, function returns
too many values".
How can I work with this multi-valued function under IDS 10?
Ideally, I would like to treat it as a virtual table, joining it
to other tables, etc.
I know I could create another UDR and use
FOREACH execute my_function INTO varX, varY
but I would really rather not process this data row by row.
What are my options here? I've tried to make sense of the
documentation on collection derived tables, multisets and the
like, but the docs just don't have enough detail or practical
examples.
Thanks,
Jeff
Look up MULTISETs. You can cast the function return to a MULTISET and then
put the MULTISET into the TABLE clause.
Derived tables are first supported in 11.10, that's why you are having
trouble in 10.00.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Apr 24, 2009 at 5:34 PM, Jeff <jlar310@yahoo.com> wrote:
> I have a complex UDR that uses RETURN x, y WITH RESUME to
> generate a list of values.
>
> I have found that with IDS 11, I can do
>
> SELECT * from TABLE(my_function(param));>
> but with IDS 10, the same statement gets "-684, function returns
> too many values".
>
> How can I work with this multi-valued function under IDS 10?
> Ideally, I would like to treat it as a virtual table, joining it
> to other tables, etc.
>
> I know I could create another UDR and use
>
> FOREACH execute my_function INTO varX, varY
>
> but I would really rather not process this data row by row.
>
> What are my options here? I've tried to make sense of the
> documentation on collection derived tables, multisets and the
> like, but the docs just don't have enough detail or practical
> examples.
>
> Thanks,
>
> Jeff
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Art Kagel wrote:
> Look up MULTISETs. You can cast the function return to a MULTISET and
> then put the MULTISET into the TABLE clause.
>
> Derived tables are first supported in 11.10, that's why you are having
> trouble in 10.00.
>
I was trying that (SELECT * FROM TABLE(MULTISET(my_function(param)))
but multiset seems to require a SELECT...
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com <http://www.oninit.com>)
> IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
>
>
> On Fri, Apr 24, 2009 at 5:34 PM, Jeff <jlar310@yahoo.com
> <mailto:jlar310@yahoo.com>> wrote:
>
> I have a complex UDR that uses RETURN x, y WITH RESUME to
> generate a list of values.
>
> I have found that with IDS 11, I can do
>
> SELECT * from TABLE(my_function(param));>
> but with IDS 10, the same statement gets "-684, function returns
> too many values".
>
> How can I work with this multi-valued function under IDS 10?
> Ideally, I would like to treat it as a virtual table, joining it
> to other tables, etc.
>
> I know I could create another UDR and use
>
> FOREACH execute my_function INTO varX, varY
>
> but I would really rather not process this data row by row.
>
> What are my options here? I've tried to make sense of the
> documentation on collection derived tables, multisets and the
> like, but the docs just don't have enough detail or practical
> examples.
>
> Thanks,
>
> Jeff
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Like this:
SELECT * FROM TABLE(MULTISET(execute my_function(param))
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Fri, Apr 24, 2009 at 7:40 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> Art Kagel wrote:
> > Look up MULTISETs. You can cast the function return to a MULTISET and
> > then put the MULTISET into the TABLE clause.
> >
> > Derived tables are first supported in 11.10, that's why you are having
> > trouble in 10.00.
> >
>
> I was trying that (SELECT * FROM TABLE(MULTISET(my_function(param)))
> but multiset seems to require a SELECT...
>
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com <http://www.oninit.com>)
> > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization with which I am associated either explicitly or implicitly.
> > Neither do those opinions reflect those of other individuals affiliated
> > with any entity with which I am affiliated nor those of the entities
> > themselves.
> >
> >
> >
> > On Fri, Apr 24, 2009 at 5:34 PM, Jeff <jlar310@yahoo.com
> > <mailto:jlar310@yahoo.com>> wrote:
> >
> > I have a complex UDR that uses RETURN x, y WITH RESUME to
> > generate a list of values.
> >
> > I have found that with IDS 11, I can do
> >
> > SELECT * from TABLE(my_function(param));> >
> > but with IDS 10, the same statement gets "-684, function returns
> > too many values".
> >
> > How can I work with this multi-valued function under IDS 10?
> > Ideally, I would like to treat it as a virtual table, joining it
> > to other tables, etc.
> >
> > I know I could create another UDR and use
> >
> > FOREACH execute my_function INTO varX, varY
> >
> > but I would really rather not process this data row by row.
> >
> > What are my options here? I've tried to make sense of the
> > documentation on collection derived tables, multisets and the
> > like, but the docs just don't have enough detail or practical
> > examples.
> >
> > Thanks,
> >
> > Jeff
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
> >
>
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Art Kagel wrote:
> Like this:
>
> SELECT * FROM TABLE(MULTISET(execute my_function(param))>
>
No. That gives -201 (at least in 11.50, so it should do the same on 10.
After checking the fine manual (SQL syntax, page 2-499) this works:
SELECT * from TABLE(FUNCTION my_function(param))
Regards
> Art S. Kagel
> Oninit (www.oninit.com <http://www.oninit.com>)
> IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
>
>
> On Fri, Apr 24, 2009 at 7:40 PM, Fernando Nunes <domusonline@gmail.com
> <mailto:domusonline@gmail.com>> wrote:
>
> Art Kagel wrote:
> > Look up MULTISETs. You can cast the function return to a
> MULTISET and
> > then put the MULTISET into the TABLE clause.
> >
> > Derived tables are first supported in 11.10, that's why you are
> having
> > trouble in 10.00.
> >
>
> I was trying that (SELECT * FROM TABLE(MULTISET(my_function(param)))
> but multiset seems to require a SELECT...
>
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com <http://www.oninit.com>;
> <http://www.oninit.com>)
> > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>
> <mailto:art@iiug.org <mailto:art@iiug.org>>)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization with which I am associated either explicitly or
> implicitly.
> > Neither do those opinions reflect those of other individuals
> affiliated
> > with any entity with which I am affiliated nor those of the entities
> > themselves.
> >
> >
> >
> > On Fri, Apr 24, 2009 at 5:34 PM, Jeff <jlar310@yahoo.com
> <mailto:jlar310@yahoo.com>
> > <mailto:jlar310@yahoo.com <mailto:jlar310@yahoo.com>>> wrote:
> >
> > I have a complex UDR that uses RETURN x, y WITH RESUME to
> > generate a list of values.
> >
> > I have found that with IDS 11, I can do
> >
> > SELECT * from TABLE(my_function(param));> >
> > but with IDS 10, the same statement gets "-684, function returns
> > too many values".
> >
> > How can I work with this multi-valued function under IDS 10?
> > Ideally, I would like to treat it as a virtual table, joining it
> > to other tables, etc.
> >
> > I know I could create another UDR and use
> >
> > FOREACH execute my_function INTO varX, varY
> >
> > but I would really rather not process this data row by row.
> >
> > What are my options here? I've tried to make sense of the
> > documentation on collection derived tables, multisets and the
> > like, but the docs just don't have enough detail or practical
> > examples.
> >
> > Thanks,
> >
> > Jeff
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> <mailto:Informix-list@iiug.org <mailto:Informix-list@iiug.org>>
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
> >
>
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
works on 10.00.UC4
create procedure test(number smallint)
returning smallint as value; define value smallint;
for value = 1 to number
return value with resume;
end for
end procedure;
select * from table (function test(10)) as test_alias(test_column);
this one, multi-valued, works too on 10.00.UC4
create procedure test(number smallint)
returning smallint as value, int as v1; define value smallint;
for value = 1 to number
return value, value with resume;
end for
end procedure;
select * from table (function test(10)) as test_alias(test_column,test2);