You can translate the question and the replies:

Executing Dynamic VQL generated from view output

Hi, We have a list of report views that are cached for which we need to generate the report which shows the count or records for all those views. There is a view we have which could give the names of these views that we have to get the counts on. Is there a way to execute those count queries based on that list since it might change over time. For Eg. Output of the view will be like this : Row 1 : Select count() from view1; Row 2 : Select count() from view2; and so on These rows of output needs to be executed to get the counts. For this use case do we need to create a Java Stored procedure in denodo or if there is some other esasier way this could be done. Any suggestions would be appreciated.
user
19-11-2019 02:06:58 -0500

1 Answer

Hi, If I wanted to create a report view like the one you are describing I think I would take a slightly different approach. What I would do, assuming you already have a view with a field with the names of the views you want to report, is: 1. [Create a data source](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources) pointing to the own VDP server. 2. [Create a base view from a query](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries). In that query I would include an [interpolation variable](https://community.denodo.com/docs/html/browse/7.0/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) so the query selects the count(*) of the view inserted in that variable. 3. Finally create a [Join view](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_join_views/creating_join_views) between the view with the names and the view just created from the query This way the interpolation variable is substituted with the names of the views and on the resulting view I’d have the name of each view and the count(*) of it. Hope it helps!
Denodo Team
20-11-2019 18:54:48 -0500
You must sign in to add an answer. If you do not have an account, you can register here