You can translate the question and the replies:

Denodo Cached View Issue (returns 0 records)

I am seeing a strange issue with my Denodo cached view where it returns 0 records if I put a where statement and limit the columns. No issue if I select all columns though. Is this a bug, or I am missing the error? Please help me get the tailored view to return records - I am ultimately trying to do a group by on this cached view, but adding the group by has the same effect as simply selecting specific columns. Query that works but isn't tailored for my needs: ![Working Query](https://i.stack.imgur.com/WOyie.png) Execution trace from the working query for reference: ![Execution Trace](https://i.stack.imgur.com/hVr5d.png) Query that demonstrates the issue by selecting specific fields from the above attempt: ![Query with Issue](https://i.stack.imgur.com/f4v4J.png) Execution trace from the query with issue: ![Execution Trace Issue](https://i.stack.imgur.com/SUZJh.png) Cached view settings (updates from source for date >= last refresh in order to catch any new records): ![Cache View Settings](https://i.stack.imgur.com/iSIAG.png) Any suggestions or advice appreciated - thank you!
Cache Full Cache Query WHERE CONDITIONS
user
23-09-2019 14:30:19 -0400
code

5 Answers

Hi, I was able to retrieve the results from a view when the incremental cache mode was enabled as well as when I used the GROUPBY operation. So, in situations where I do not get the expected results, I would check the [Execution Trace](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/querying_views/execution_trace_of_a_statement) which shows in detail about the query which contains information about the process of storing the data in the cache database. Also, I would make sure that all requirements are met for using incremental mode by referring to this [link](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/cache_module/cache_modes/incremental_mode#requirements-a-view-has-to-meet-to-support-incremental-queries). Hope this helps!
Denodo Team
25-09-2019 09:04:01 -0400
code
Sorry, but did you read my description? I included screenshots of both the execution trace and cache settings. I've read the canned documentation already, and I still can't figure out what I'm doing wrong. It would be helpful for someone on the Denodo team to provide tailored answers so that if anyone else is having the same issue we can see how to resolve. I included a lot of detail so it would be easy for others to follow, but if you need more information on the issue please let me know what else I could provide.
user
 Edited on: 26-09-2019 17:29:47 -0400
Hi, It is successfully working on my end. So, in your situation, I would troubleshoot this issue by performing the below steps: 1. I would check whether the incremental cache join condition has been applied on the primary key defined on the base view. 2. Then, I would analyze the execution trace in detail to check the SQL sentences that has been applied on the data source as well as cache and then would check the join condition that has applied to fetch the records. 4. If the above options are unsuccessful, then I would change the cache mode to Full Cache and check if it works fine (for both selecting the full view i.e., "select * from <view>" as well as selecting particular fields of the view "select column1, column2 from <view>) Hope this helps!
Denodo Team
10-10-2019 08:49:09 -0400
code
Thank you for these tips! 1. I checked the cache join condition is with primary key, so this looks to be ok. 2. I am still seeing the same execution trace info as I posted in the original question. The SQL sentences applied to the data sources are correctly returning rows as shown in the black boxes. However, the join of cached and non-cached data is still returning 0 rows as shown in the red box in the third image. I don't know how to see the logic used in the FULL OUTER JOIN in the red box to know exactly why it is returning 0 rows with an outer join of tables that have 11805 and 225 rows. It should be producing 12030 rows (or hitting the 10000 limit). This is where I am confused. 3. I did try a Full Cache version of this table, and you are correct that the full cache works as expected returning all the data. For now I can use a view of the full cache for history plus the live view for current date and set up a procedure to update the full cache each day. This is not ideal as it is more work to manage the cache update instead of letting it update when users hit the partial cache view, but I believe it should provide the same performance for my users and allow viewing of most up to date records so this will work for now. If you are able to provide more advice as to how I can troubleshoot #2 to get partial cache to work, that would help me maximize efficiency with Denodo's capabilities. Thanks again for the suggestions and helping me get the full cache version to use for now!
user
10-10-2019 11:47:46 -0400
Hi, In order to check the logic behind on how the Incremental cache works, I would expand the [execution nodes](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/querying_views/execution_trace_of_a_statement) where the FULL OUTER JOIN is being performed of the Execution Trace of the 'bv_bsm_stored_daily_cache' view and then check whether the join condition correctly returns the records from the source as well as the Cache. Additionally, if you still need help and if you are a valid Support User, you may open a **Support Case** at the [Denodo Support Site](https://support.denodo.com/) and the Support Team will help you. Hope this helps!
Denodo Team
18-10-2019 07:33:53 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here