Re: Continuous request against Procedure Execution

[markaddleman]

Hi,

I think I'm in need of some basic SQL tutorial.

Ideally, I would like to issue a continuous query of the form:

WITH timerange AS (select * from TABLE(call Times.timerange(start, end, period)))

      SELECT * FROM table_with_time_element t WHERE t.timestamp BETWEEN timerange.start AND timerange.end

It seems to be a concise and meaningful query that, in effect, causes Teiid to re-execute the inner select on a periodic basis.  What is bugging me is the semantics of the WITH clause:  Everywhere I read about WITH, it indicates that it effectively creates a temp table.  If so, even though the timerange stored proc is a reusable execution, my guess is that Teiid only queries it for values once, creates its temp table and then executes the inner SELECT continuously.  Of course, this isn't what I wanted but WITH may be the wrong way of going about it.

[shawkins]

Mark,

The concept of a continuous query is out side of how the sql is expressed.  Or to put it differently, the sql specifices how each iteration of the continuous query should be executed.  The engine starts over each time. Only ReusableExecutions are aware that they are being called multiple times within the scope of the continuous query.

As you had expressed before there is a desire for some stuff to be cached beyond a single iteration.  https://issues.jboss.org/browse/TEIID-1598 provides one avenue at the translator level using the session scope.  https://issues.jboss.org/browse/TEIID-2077 is another possible hook as well.

Steve

[markaddleman]

Thanks for the explanation, Steve.  I was actually hoping that that's how it operated and I think it's exactly the right behavior.  I didn't make it clear in my response:  I was trying to find an explanation for some the  behavior that Andriy is seeing.  It looks like Andriy and Ramesh are working through the details, so I'll stop sticking my nose into it.