Creating a view from a Denodo Stored Procedure

Hello, Is it possible to create a view using a Denodo Stored Procedure or incorporate a Denodo Stored Procedure in an existing view. I need to count the total number of rows in the output of a view and then take that total count and include it in that same view with the output I just counted. I have been unsuccessful at this point because of deadlock errors, analytic function errors among others. If I am correct,Denodo has a stored procedure that can count the number of rows. If that is the case I was thinking I could just use that stored procedure to count the rows for me. Is it possible to use the stored procedure in the view I am doing the count of in order to attain the number I need, or is it possible to create a view using the Denodo stored Procedure? Thanks!
user
08-08-2019 09:28:59 -0400

4 Answers

Hello again, below is an example of what I have in mind. The view I am working with is called dv_mbr008_step_n_13_fdssp_call_logs_cca_p3_counts. In the view I need to count the number of rows the view returns and include that number in the view. If my understanding is correct, if I use the stored procedure CALL GENERATESTATSFORFIELDS with the syntax below for my view that it will return a total count of the rows in my view. The one problem I have is that I only need the row count for a certain time period...ie. using the date parameters I have in the view. Is it possible to not only create a view off of the syntax below but also to run the stored procedure below with a parameter. I only need the row count with my paramaert p_run_date = '03/31/2019' Thanks! CALL GENERATE_STATS_FOR_FIELDS ( 'dv_mbr008_step_n_13_fdssp_call_logs_cca_p3_counts', {} )
user
08-08-2019 10:39:26 -0400
Hi, In Virtual DataPort, It is possible to create a view using a Denodo Stored Procedure. To get more information you can have a look at [Stored Procedures](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/stored_procedures/stored_procedures) of the Virtual DataPort Administration Guide. In Virtual DataPort, the analytic function does not work, so it must be Pushed down to the underlying data source. If your underlying source does not support the analytic function then it will throw the analytic function errors. To get the number of rows of the view based on some condition. I would use “count” instead of Stored Procedure because GENERATE_STATS_FOR_FIELDS is used to gather and stores the statistics of the fields of a view to be used by cost-based optimizer. Just keep mind that GENERATE_STATS_FOR_FIELDS(<view_name>,{}) is used to gather the number of rows and stored in the statistics of that view it does not return any values. For the above scenario, I would do the following steps * In Virtual DataPort, select the data source and click the create from query. * In “create from query”, use the count function to get the total number of rows, as below > Select count(*) from <view_name> where <filed_name>=<value> * Now I was able to get the row count of that view. By using the count function the query can be delegated to the underlying data source. so, the performance of the query can be increased. For more information, you can refer to the document [Creating Base Views from SQL Queries](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) of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
09-08-2019 08:01:45 -0400
Hello, Thank you for the post. Quick question, the view I need a count off of is from denodo and not from the source. I tried to do what you suggested but I got the following error com.denodo.util.exceptions.InternalErrorException: ERROR: relation "dv_mbr008_step_n_13_fdssp_call_logs_cca_p3_counts" does not exist Position: 22 Here is the query I tried to run select count(*) from dv_mbr008_step_n_13_fdssp_call_logs_cca_p3_counts The view I need a count from is a denodo view I created. Is my syntax incorrect or am I not allowed to create a view this way from the source when the view I am pulling from is a denod view and not a view from the source. Thanks
user
09-08-2019 08:22:09 -0400
Hi, The above error is a Postgresql data source error. This error occurs when you try to create a view in Virtual DataPort for the table not available in the underlying data source or table name is incorrect. For example, I have created a table in the Postgresql name “emp_Id” when I try to create the view in Virtual Dataport using “create from query ” option select * from emp_id instead of “emp_Id” it will throw the same error because in the PostgreSQL both are two different tables. To get a total count of the output rows for the view created in the Virtual DataPort, I would do the following steps * Create a “Selection view” on top of the base view and add a new “Aggregate field” with field expression. >   Count(*) * Create another “Selection view” on top of the base view with where condition and to pass the value for where condition at runtime I would use [View parameter](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#parameters-of-derived-views).  * Create join between the above two selection view and after creating that view I was able to get the record count and data in a single view. For more information, you can refer to the document [Creating Selection Views](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#creating-selection-views) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
19-08-2019 03:04:04 -0400
You must sign in to add an answer. If you do not have an account, you can register here