You can translate the question and the replies:

Joining View Created From Stored Procedure to View Created from "Create from query" option - Not Working

Hello, Can a view created from a stored procedure be joined with a view created from the "Create from query" option? I have a report that runs perfectly now. The report is generated from a view created from a stored procedure joined to a view created the normal way. However; now when I try to recreate the exactly report but instead of joining the view from the stored procedure to a view created the normal way, I am not trying to join it from a view created from "Create from query" the report does not run. There is no different in the tables used and criteria between the two approaches. The only difference is one view is created the standard way and the other is created by the "Create from query" option. Both are pulling from the same tables. Thanks
user
09-07-2019 13:47:22 -0400
code

5 Answers

Hi, I tried to perform a join between the view created from a stored procedure and view created using the Create from query option and it worked fine for me without any errors. In this scenario, I would perform the following checks in order to get more information: * Check the Execution Trace under the Query Results tab of the view to check the query executed at the source. * Check the vdp.log file located under the directory <Denodo_Home>\logs\vdp. In the Execution trace, you may look for the errors in the failed nodes that will help you to understand the root cause. For more information, you can refer to the [Execution Trace of a Statement](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/querying_views/execution_trace_of_a_statement) and[ Execution Trace Information](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/appendix/execution_trace_information/execution_trace_information#execution-trace-information) Section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
10-07-2019 07:26:58 -0400
code
Thanks. I will take a look. There is one more thing that I found this morning that caught my attention that I am hoping can help us determine the issue. When I run the report to pull a single record, the report works. It is only when I run the report to pull more than one record is when I run into the issue. Thanks again, hopefully this information will help us come closer to resolving the issue. In the meantime I will follow the steps you suggested below. Thanks
user
10-07-2019 08:17:46 -0400
Hellow again. If this helps, below is the error I am getting when I try to pull more than one record. Finished with error: Error executing query. Total time 5.885 seconds. QUERY [PROJECTION] [ERROR] QUERY [SELECTION] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [PROJECTION] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [SELECTION] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [PROJECTION] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [VIRTUAL] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [JDBC WRAPPER] [ERROR] DV_FDSSP_MBR008_ID_HNDBK [JDBC ROUTE] [ERROR] Received exception with message 'ERROR: function cannot execute on segment because it issues a non-SELECT statement (entry db 172.28.8.250:5432 pid=286522) Detail:
user
10-07-2019 08:32:49 -0400
Hi, I was able to get results for both the single and multiple records from the base view created as "Create from query" option against my SQL Server. I googled the error and the data source you are using appears to be Greenplum Database, I believe the Greenplum has limitations on the function created and this might be an error related to the Greenplum syntax. I would try the same query which you used on the create from query pane on a generic JDBC Clients like DBVisualizer or DBeaver and try the outcome. The Greenplum community would I believe is the right place to post the question with a detailed error message. Hope this helps!
Denodo Team
17-07-2019 08:21:25 -0400
code
Thanks!
user
17-07-2019 09:46:41 -0400
You must sign in to add an answer. If you do not have an account, you can register here