You can translate the question and the replies:

Query Regarding UNION ALL

Hi Team, I have created a multiple node in denodo connecting to Jethro Database. All these nodes are working fine and gives the proper result. Now when I am doing UNION of all these nodes, then denodo is not giving result. It says "java.sql.SQLException". When I checked, I found that Jethro does not support "UNION". It only support "UNION ALL" syntax. And the virtual query that Denodo generate is having "UNION" and hence it is giving error. Now is there any way by which I can accomplish my work in denodo or is there any work around thru which I can see all the records from those nodes? Note : I tried below property but still it is giving same error: SET 'com.denodo.vdb.union.enableStandardSQLUnion' = 'true';
user
04-03-2020 11:53:12 -0500

1 Answer

Hi, In Denodo, the UNION clause acts like an EXTENDED UNION and includes duplicate values to increase performance (like a UNION ALL clause). By running the command: `SET ‘com.denodo.vdb.union.enableStandardSQLUnion’ = ‘true’;` The operations UNION [ DISTINCT ] and UNION ALL will behave as defined by the SQL standard. It will be possible to continue using the extended union operation using the operator EXTENDED UNION ALL. You can find more information about the Denodo [UNION Clause](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/union_clause/union_clause) in this link to the Virtual DataPort Administration Guide. Returning to the error: “java.sql.SQLException” is thrown by the JDBC driver when the data source cannot execute the SQL query given to it. In order to figure out why this error is occurring, I would start by consulting the following: * The “vdp.log” file. This is located in the “<DENODO_HOME>/logs/vdp” directory and contains more information about errors encountered by Virtual DataPort. * The Execution Trace. This contains information about the SQL statement that was delegated to the data sources. In the Execution Trace, after clicking on the data source and expanding the “Advanced” tab, you will be able to find an entry called “SQL sentence” that contains the query that was sent to the database. For more information about connecting Denodo to Jethro, you can see the [How to connect to Jethro from Denodo](https://community.denodo.com/kb/view/document/How%20to%20connect%20to%20Jethro%20from%20Denodo?category=Data+Sources) article in the Knowledge Base. Additionally, more information about using the [Generic Adapter](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#generic-adapter) can be found in the JDBC Sources section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
05-03-2020 20:55:42 -0500
You must sign in to add an answer. If you do not have an account, you can register here