-
1. Re: How to get LIMIT to work as desired w/virtual procedure?
rareddy Aug 17, 2015 9:29 PM (in response to mkitchin)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
-
2. Re: How to get LIMIT to work as desired w/virtual procedure?
shawkins Aug 18, 2015 8:44 AM (in response to rareddy)> 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.
-
3. Re: How to get LIMIT to work as desired w/virtual procedure?
mkitchin Aug 18, 2015 1:50 PM (in response to shawkins)Hi there,
@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.
-
4. Re: How to get LIMIT to work as desired w/virtual procedure?
shawkins Aug 18, 2015 1:57 PM (in response to mkitchin)> 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.