You can translate the question and the replies:

Records missing in a derived view, but the data exists in those base views or the source tables even after applying the same join conditions which are being used in derived view.

Hi All, I am encountering this wierd scenario in denodo. 3 records are not displaying when I query based on the name, and 3 records are being displayed when I query based on the id from the derived view. Denodo version: denodo platform 5.5 I have table1, table1 & table3 I am joining these tables in denodo. In the vql shell and also in the source database Query: select table1.name, table1.id, table1.dob, table1.joindate, table2.location, table3.location_name from table1, table2 where table1.id=table2.pid and table1.locale=table2.location and table1.locale=table3.location_name and table1.name ='tom' Results: tom, 1000, 1/11/1986,1/1/2010,chicago,chicago tom, 1001, 10/11/1988,11/1/2011,new york,new york tom, 1121, 5/1/1978,3/10/2012,dallas,dallas if I run the query using the derived view: Query1: select dv1.name, dv1.id, dv1.dob, dv1.joindate, dv1.location, dv1.location_name where dv1.name ='tom' Results for query1: tom, 1000, 1/11/1986,1/1/2010,chicago,chicago when i checked the execution trace, it shows HASH JOIN & SORTER MERGE when it is joining table1_table2 with table3. Query2: select dv1.name, dv1.id, dv1.dob, dv1.joindate, dv1.location, dv1.location_name where dv1.id in (1000,1001,1121) Results for query1: tom, 1000, 1/11/1986,1/1/2010,chicago,chicago tom, 1001, 10/11/1988,11/1/2011,new york,new york tom, 1121, 5/1/1978,3/10/2012,dallas,dallas Please advise why the 3 records are not displaying when I query based on the name. and 3 records are being displayed when I query based on the id.
derived view hash join sort merge missing records resultset not same with different where conditions
user
02-07-2016 15:56:19 -0400
code

3 Answers

Hi! Taking a look at your query, it doesn’t seem correct because *table3* doesn’t appear on the From clause. Regarding the execution trace you mentioned, I would check if the 3 tables are over the same source. It seems the Join is not being delegated to the source and, if they are from the same JDBC source, VDP will delegate the Join to the source by default. Finally, I would also check if the *“name”* attribute in your derived view (*dv1*) comes from the *table1* *“name”* attribute. Hope this helps!
Denodo Team
06-07-2016 08:35:33 -0400
code
Hi Denodo Team, Thanks for the response, yes, its typo, I missed to copy table3 in the above question. Corrected sql query: select table1.name, table1.id, table1.dob, table1.joindate, table2.location, table3.location_name from table1, table2, table3 where table1.id=table2.pid and table1.locale=table2.location and table1.locale=table3.location_name and table1.name =’tom’ and table1.system='ABCD' Yes, execution trace says "The specified join type cannot be delegated to this database".Join Type=HASH and Execution Strategy = Sort merge. this is the join between table1 and table3. yes, the name attribute is coming from table1. but still I did not get to understand why it is not display all the 3 records when filtered by dv1.name='tom' and it displays all the 3 records where dv1.name='tom' and dv1.id in (1000,1001,1121) can you please help me. Thanks.
user
06-07-2016 13:13:13 -0400
Hi! If the involved views are created over the same datasource, the Join should be delegated to the source. If the join is not delegated, I would review the execution trace of your queries in order to determine why this occurs: In the lowest level of the *Execution trace* you can get information about the executed queries on the sources and the number of rows obtained by each one. I would also check the filters applied for each query. In addition, you can check the Join level of the execution trace to review the applied filters in the Join. Hope this helps!
Denodo Team
07-07-2016 09:07:59 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here