Analytical functions not delegated to the cache database

Why are the analytical functions not delegated to the cache database? The cache database is Oracle. This query on a cached table: SELECT code, omschr, vervallen, lag(code) over (partition by vervallen order by code) FROM peter_test_cache_cszislib_artstype Gives this error: Finished with error: Error executing view: Function lag is not executable When I create a base view for the cached table on the datasource "customvdpcachedatasource" and run this query: SELECT code, omschr, vervallen, lag(code) over (partition by vervallen order by code) FROM c_slib_artstype4340124793144 I get the desired results VQL for the caches table is: CREATE OR REPLACE VIEW peter_test_cache_cszislib_artstype FOLDER = '/20 - data laag/conversie' AS SELECT cszislib_artstype.code AS code, cszislib_artstype.omschr AS omschr, cszislib_artstype.vervallen AS vervallen FROM cszislib_artstype; ALTER VIEW peter_test_cache_cszislib_artstype LAYOUT (cszislib_artstype = [20, 20, 200, 86]); ALTER VIEW peter_test_cache_cszislib_artstype CACHE FULL BATCHSIZEINCACHE DEFAULT TIMETOLIVEINCACHE NOEXPIRE;
user
13-09-2017 10:33:10 -0400

4 Answers

Hi, This error has occurred, because Virtual DataPort could not delegate analytical functions to the underlying data source. You could refer this [link](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#lag) to know about “lag” function in Virtual DataPort. You could check the “Execution trace” of the query in Virtual DataPort to ensure that the data is retrieved from the Cached data source. As Oracle database supports “lag” function the query would be executed without errors. When I execute a query with analytical functions in a JDBC/ODBC datasource with cache 'off', I would enable the 'Delegate Analytic function list' in the "JDBC Datasource >Source Configuration tab". For other type of data Sources, I would enable the cache and execute the query to delegate the analytical function to data source. You could refer the section [Execution Trace of a Statement](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/querying_views/execution_trace_of_a_statement#execution-trace-of-a-statement) and to get detailed information on the query execution trace. Hope this helps you.
Denodo Team
14-09-2017 09:26:23 -0400
Sorry, but this answer doesn't help me. ‘Delegate Analytic function list’ is enabled in de data source for the cache database. Cache database is Oracle, which supports windowing functions. I can't get the execution trace, because Denodo gives an error: "Finished with error: Error executing view: Function lag is not executable" and execution trace is greyed out. Why is the query not delegated to the cache database?
user
14-09-2017 11:28:49 -0400
The answer from the Denodo team suggest that it should be possible to delegate analytical functions to the cache database. How do I control delegation of analytical functions to the cache database?
user
19-09-2017 04:21:50 -0400
Hi, This issue could have occurred when the Analytic functions is not supported by the external cache data source. I would ensure that the view uses the Oracle cache data source, to delegate the LAG function. Hope this helps!!
Denodo Team
12-10-2017 06:26:26 -0400
You must sign in to add an answer. If you do not have an account, you can register here