You can translate the question and the replies:

Cache job for a view with dynamic where clause

Hello, I have a view that I would like to cache with a where clause, so that it picks up only a few rows. For example: select * from test_view where test_log_id > **max(test_log_id) ** CONTEXT('cachepreload'='true', 'cachewait_for_load'='true', 'cache_return_query_results'='false', 'cache_invalidate'='all_rows', 'cache_atomic_operation'='true') 1. The above query does not work as the cache query does not accept group functions. 2. If I get the max value separately and substitute it in the above query, then the cache works. I would like to know, how can I automate this process with the scheduler? Currently the non-parametrized query option is available only for scheduler jobs of type 'VDP' and not for 'VDPCACHE' Any ideas/suggestions? Thanks.
19-07-2022 15:38:51 -0400

2 Answers

Hello, In order to have the **Query (non parameterized)** option available in the extraction section of the VDPCache jobs, you can do the following:. For example: If you create a VDPCache type job over the test_view, * In the query conditions box, write **test_log_id > ‘@maxid’** (which is comparing the field test_log_id to an interpolation variable @maxid which can be used for dynamic values) * then an add data source option will appear below, once you choose the data source, the data source, **Query (non parameterized)**, and mappings option will be available. Please refer to [VDPCache Extraction Section](,VDPCache%20Extraction%20Section,-To%20configure%20the) documentation for guidelines. In your scenario, the above can be achieved by creating a view from query, and refreshing the cache ,for example, VQL shell: **Create view test_view1 as (select * from test_view where test_log_id>(select max(test_log_id) from test_view))** From my understanding of the question, the max(test_log_id) and test_log_id refers to the same view test_view, then you will get 0 records when cache is updated, it is possible to pass dynamic values by using interpolation variables. Interpolation variables can store dynamic values, you can refer to the [Parameters of Derived Views](,Parameters%20of%20Derived%20Views,-A%20view%20can) documentation on how to use interpolation variables as parameters and in where clause. In case you need any further assistance and If you have a valid support account, I recommend opening a support case on the [Denodo Support Site]( Hope this helps!
Denodo Team
20-07-2022 17:07:32 -0400
Thanks for the response, will try the above suggested scenario. Just wanted to let you know that I was able to achieve the same result by selecting the job type as 'VDP' and defining the parameterized and non-parametrized queries as below : **Parameterized query** : select * from test_view where test_log_id > @max_test_log_id CONTEXT('cache_preload'='true', 'cache_wait_for_load'='true', 'cache_return_query_results'='false', 'cache_atomic_operation'='true') **Non-Parameterized query** : select max(test_log_id) as max_test_log_id from b_enr_usage2_cache Thanks.
20-07-2022 23:03:47 -0400
You must sign in to add an answer. If you do not have an account, you can register here