You can translate the question and the replies:

View with parameter (drop down list value)

i created a view with a parameter (e.g. p_processing_month) and in the where condition i put the condition <view>.processing_month = p_processing_month at run time i would like to have a drop down list for p_processing_month showing the distinct values of processing_month from the view. is that possible? e.g. if select distinct processing_month from <view> returns 2021-10-31, 2021-11-30, 2021-12-31 then at run time i would like to be able to select for p_processing_month one of those 3 values thanks
user
08-02-2022 07:45:13 -0500
code

5 Answers

Hi, In order to pass the values dynamically, I would use the interpolation variable. By using interpolation variables, paths and other parameters of data sources can be parameterized. Hence, I can provide the values of a field at runtime.For more information, you can have a look at section [Creating Base Views from SQL Queries](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries) of Virtual DataPort Administration Guide.In your scenario,I would perform the following steps instead of using drop down list in View Parameters * Create the intended base view * Create another base view using 'create from query' option with interpolation variable that includes a DISTINCT clause in the query to get the list of values for the column in scope (in this case , date) * Create a join view to perform inner join between the above two views to filter data by providing the required value for the interpolation variable. The parameter value provided to the final view filters records that satisfies the condition Hope this helps!
Denodo Team
10-02-2022 05:19:54 -0500
code
thanks for your reply however i would need a bit of more clarity i created the derived view2 from query. this gives me the list of processing_month CREATE VIEW test_processing_months AS select processing_month from view group by processing_month then i have a derived view (view1) created by GUI that has parameter p_processing_month do i have to join the two views (view1 and view 2) and in the where condition put view1.p_processing_month = view2.processing_month? thansk in advance for your reply
user
14-02-2022 03:23:38 -0500
Hi, I would create the base view with interpolation variable for the date value. You could refer to link [Interpolation variables](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/path_types_in_virtual_dataport/path_types_in_virtual_dataport#paths-and-other-values-with-interpolation-variables) on steps to create an interpolation variable in a base view.For instance I would create a interpolation variable with a value 2021-10-31 in where clause of base view which results in 1 record with value 2021-10-31 as distinct applied on date column (processing month) in scope. Perform an inner join between the above base view and derived view which filters records for 2021-10-31. The where condition can be modified in the derived view (join view) with different date value (interpolation variable) to get the required filtered records as per the where condition. Alternatively, instead of a base view the same can be achieved as view parameter in derived view as you have mentioned with a parameter p_processing_month. On both scenarios, you can provide the filters during runtime as there is no drop down list option available when selecting value for a parameter/where condition. Hope this helps!
Denodo Team
14-02-2022 07:46:25 -0500
code
thanks for your answer as specified in the chat there is no base view but only derived views. the link provided on the interpolation variables doesn'e explain how to define an interpolation variable in a derived view. are you then saying that i cannot have interpolation variable in a derived view and then i cannot achieve the drop down menu for the date value?
user
14-02-2022 07:49:59 -0500
Hi, Interpolation variables are specific to base view only and not applicable to a dervied view.Also please note that on both scenarios either with a interpolation variable in a base view or a dervied view with view parameters there are no option currently available to select the date values from a drop down list and hence the parameter value (processing month) need to be provided at runtime Hope this helps!
Denodo Team
15-02-2022 01:59:43 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here