You can translate the question and the replies:

Denodo 7.0 not invalidating matching rows with dynamic VQL, working in 6.0

I have a cache update query with the paramaters: select * from view_name where "PeriodMonth" in ( cast('text',substr(FORMATDATE('MMM', ADDMONTH(CURRENT_DATE(),-1)),1,1)||lower(substr(FORMATDATE('MMM', ADDMONTH(CURRENT_DATE(),-1)),2,3))||'-'||FORMATDATE('YY', ADDMONTH(CURRENT_DATE(),-1)))) CONTEXT('cache_preload'='true', 'cache_wait_for_load'='true', 'cache_invalidate'='matching_rows') TRACE; The where clause dynamically derives the "PeriodMonth" by looking at the current date and creating a string that matches the field i'm filtering on. In this case, the "PeriodMonth" comes out to "Jun-20". In Denodo 6.0, this will match against existing rows in the full cache denodo view and invalidate the old data. In 7.0, the old data is retained, even though the rows match. This creates duplicate rows in 7.0. If I change the query to a fixed ""PeriodMonth" = 'Jun-20'", the cache update works properly and old rows are invalidated. It seems to not work when I use the dynamic VQL to derive the string 'Jun-20'. Was there a change in 7.0? How should I proceed?
user
08-07-2020 20:58:50 -0400

1 Answer

Hi, I tried to reproduce your scenario in Denodo 7.0 but the invalidation works fine for both the below queries i.e I could see ROWSTATUS is updated for all the matching rows in the cache table and does not add duplicate rows during preload. * select * from view_name where "PeriodMonth" in ( cast('text',substr(FORMATDATE('MMM', ADDMONTH(CURRENT_DATE(),-1)),1,1)||lower(substr(FORMATDATE('MMM', ADDMONTH(CURRENT_DATE(),-1)),2,3))||'-'||FORMATDATE('YY', ADDMONTH(CURRENT_DATE(),-1)))) CONTEXT('cache_preload'='true', 'cache_wait_for_load'='true', 'cache_invalidate'='matching_rows') TRACE; * select * from view_name where "PeriodMonth" in 'Jun-20' CONTEXT('cache_preload'='true', 'cache_wait_for_load'='true', 'cache_invalidate'='matching_rows') TRACE; Additionally, I would also check if the functions used in the where condition are in the ‘Delegate scalar functions list’ in the Data source configuration and also the Delegation query in the [Execution Trace](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/querying_views/execution_trace_of_a_statement#execution-trace-of-a-statement). Hope this helps!
Denodo Team
10-07-2020 01:43:08 -0400
You must sign in to add an answer. If you do not have an account, you can register here