So want to send the LIMIT value dynamically? Definitely you do not want use it on the XMLELEMENT statement. The only thing I can think of now is Dynamic SQL Command Procedure Language - Teiid 8.12 (draft) - Project Documentation Editor
> My guess is in this case the LIMIT predicate is applied to the net output of XMLAGG which is always a single row.
That is correct.
So you would actually need an inline view to associate the limit after the where, but before the projection:
SELECT XMLELEMENT(NAME bars, XMLAGG(XMLELEMENT(NAME bar,
XMLFOREST(my_view.foo.id, my_view.foo.x, my_view.foo.y, my_view.foo.z)
))) AS result FROM (select * from my_view.foo WHERE my_view.foo.id LIKE my_view.my_proc.id limit 10) as x;
You should also be able use a parameterized limit expression if needed.
@steven: Thanks for getting back to me so quickly.
That's what we suspected. Not the end of the world, however -- we've been able to use inline views, as described.
It would be good to be able to cascade such queries more conveniently.
> It would be good to be able to cascade such queries more conveniently.
Teiid will push limits as much as possible, but it has to respect that the limit operation logically happens last. So it does make a lot of difference where you place the limit as they all have different intents that we must respect.