You can translate the question and the replies:

Populate Denodo view parameters from another table / sub query

Hi, I have a view with two data parameters as below SELECT * FROM people_data WHERE effective_date >= in_from_effective_date AND effective_date <= in_to_effective_date USING PARAMETERS ( in_from_effective_date : DATE CURRENT_DATE(), in_to_effective_date : DATE CURRENT_DATE()); However i need to pass the values of these parameters from another query for example. SELECT * FROM people_data ih, pv_time_dim td1, pv_time_dim td2 WHERE td1.calendar_yr = 2019 and td1.calendar_qtr = 1 and td1.last_qtr_dt = 1 AND in_from_effective_date = td1.calendar_date AND td2.calendar_yr = 2019 and td2.calendar_qtr = 2 and td2.last_qtr_dt = 1 AND in_to_effective_date = td2.calendar_date; But if i run the below it passes the default values CURRENT_DATE() rather than the derived value.
user
16-12-2019 07:19:41 -0500

4 Answers

Hi, I would create a view from people_data with parameters for the effective dates. Then, create another view joining pv_time_dim and pv_time_dim to get the required dates. The calendar_yr, calendar_qtr and last_qt_date can be specified in the where conditions. The final step is to create a derived view by joining the above two views. You can find more information from the documentation [here](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#parameters-of-derived-views). I hope this helps.
Denodo Team
16-12-2019 21:53:05 -0500
Thanks for the details, unfortunately the below query is being generated by OBIEE and people_data and time_dim are different entities in the business model. SELECT * FROM people_data ih, pv_time_dim td1, pv_time_dim td2 WHERE td1.calendar_yr = 2019 and td1.calendar_qtr = 1 and td1.last_qtr_dt = 1 AND in_from_effective_date = td1.calendar_date AND td2.calendar_yr = 2019 and td2.calendar_qtr = 2 and td2.last_qtr_dt = 1 AND in_to_effective_date = td2.calendar_date; Not sure if there is any other way to make it work ?
user
16-12-2019 22:45:09 -0500
There will be other consumers providing specific dates directly and others using the time_dim, hence if there is an option to get both use cases to work with a single model
user
16-12-2019 23:20:05 -0500
Hi, For this scenario, I would create a [Join](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_join_views/creating_join_views) view over the **people_data** view and **pv_time_dim** view on effective_date and calendar_date fields. This way, the values for effective_date will be obtained from the time_dim view. If I have to hardcode the values to the effective_date field, then, I would leverage the [Branch Pruning](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/removing_redundant_branches_of_queries_partitioned_unions#removing-redundant-branches-of-queries-partitioned-unions) Optimization offered by Denodo Platform which allows the query to filter the results based **only** on the people_data view. Additionally, if you still need help and if you are a valid Support User, you may open a [Support Case](https://support.denodo.com/) at the Denodo Support Site and the Support Team will help you. Hope this helps!
Denodo Team
14-01-2020 07:30:27 -0500
You must sign in to add an answer. If you do not have an account, you can register here