You can translate the question and the replies:

Dynamic Base View based on another Base view (not JOIN)

Hi, I need help with a very specific use case. I have 2 different data sources and I need to create a base view from source A filtered by some values from another base view coming from source B. This values in the second view would be a hole column, and those are currently hardcoded in the first base view. The goal is to automate this by dynamically filter the first base view by the values from the second base view. The issue is that I can not just create a JOIN view using 2 Base views because the base view coming from source A is too big (~70GB) so it will never run due to the size. I need to apply the filter **at a Base view level.** I've read the documentation about **interpolation variables** and the **@whereexpression** option but I have not found how to **dynamically** set the values for those variables based on another base view. I was trying to, somehow, make the below query work by using an inteprolation variable inside the IN statement. select * from tableFromSourceA tableA where tableA.field in (@resultFromBaseViewB) and then dynamically replace the interpolation variable as shown below select * from tableFromSourceA tableA where tableA.field in (select viewB.field from viewFromSourceB viewB) But based on what I read, you need to provide the values for the variables at the moment you run them, not as a result of a query applied to a different base view that uses a different data source. I appreciate the help you can provide me. Regards.
user
10-11-2020 11:07:46 -0500

5 Answers

Hi, In this scenario, since two data sources are involved, I think forcing a NESTED join could be one of the way to achieve what you are looking for. I would perform the following steps: [Assuming the base views like view_A (consisting of ~70 GB worth data) and view_B are already created in Virtual DataPort.] * Firstly, I will [Create a JOIN view](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_derived_views/creating_join_views/creating_join_views#creating-join-views) between view_B and view_A and I would model it such that view_B is in the left side of the design. * Then, I will establish a Join Condition as required. * Under the Join Conditions tab, I would choose the Join Method as **NESTED**. * Save the view. Now, if I execute this Join view, first the results from the left side (view_B) of the Join will be obtained and then a query to the view of the right side (view_A) will be executed based on the results of the first view. You could take a look at [Optimizing Join Operations](https://community.denodo.com/docs/html/browse/latest/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations) section under the Virtual DataPort Administration Guide for more details. Hope this helps!
Denodo Team
11-11-2020 05:59:03 -0500
Hello, Thank you very much for the answer and the possible approach, I'd like to try it. The issue is that we are assuming that the viewA is already created with no filters at all, when acturally is not and it can not be created without the filter due to the data size. That's why I said I was trying to make that filter **at a base view level**. So do you think is possible to make this with another approach that allows me to create the base view filtered by the other base view? Or is just not possible? Thanks!
user
11-11-2020 12:37:50 -0500
Oh, an important detail. My base view-A is being created from a query, so that has some implications right? Because right now, if I try to no use the filter in that query and try to source refresh, it gives me a timeout. If this view was created not from a query but from the denodo UI by choosing the table/view in the data source, it does not need to execute the query against the database right? If so, I wouldn't get a time out and I would have the base view created without any filter. Is that correct? Does it make sense? Thanks again.
user
11-11-2020 17:48:41 -0500
Hi, Generally it is recommended to create a JDBC base view graphically rather than using the Create from Query option. You can find the Pros & Cons of this approach in the Knowledge Base (KB) article [Using the Create Base View From Query Option](https://community.denodo.com/kb/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option?category=Data+Sources). Also, note that after creating the base views, you can also create different [SELECTION](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views) views if any kind of filtration is required. About the initial question, I still think forcing a NESTED JOIN based on the steps specified before would be a good option. This will ideally fetch the results from the view which is modeled in the left side of the Join and then a query to the view of the right side will be executed based on these results. In my case, I had two different JDBC data sources and NESTED join worked as expected. If you still need help and if you are a valid Support User, you may raise a Support Case in the [Denodo Support Site](https://support.denodo.com/) so that our support team can help you. Hope this helps!
Denodo Team
18-11-2020 06:09:39 -0500
Thanks for all the recomendations. Must say that the NESTED approach was the key to solve this problem and even improved the performance when processing the data. Thank you!
user
04-01-2021 12:18:07 -0500
You must sign in to add an answer. If you do not have an account, you can register here