You can translate the question and the replies:

Union View not working for Project Requirement

Team, I have 2 views - which is for different data source. View1 = institutional_fund_indicative_data View2 = comp_fund_details_prp Common columns are: fixed_comp_fund_code, investment_vehicle, broad_investment_capability Also it has its own date fields to query the results based on date & time. Individual Queries for these 2 Views 1) View1 = institutional_fund_indicative_data institutional_fund_indicative_data_filter = ' {10044} eq ''2000'' AND {711} eq ''DMFS'' AND {15521} eq ''20151231''' and institutional_fund_indicative_data_select = '{1172} as investment_capability,{15891} as related_account_name' and institutional_fund_indicative_data_hostname = 'idwapi' 2) View2 = comp_fund_details_prp v_as_of_date = '2015-12-31' and fixed_comp_fund_code = 'DMFS' For my query the data will never return from both views at the same time. It will be either from view1 or view 2 for the where condition.Meanings -data will be available in one of the data source , not from both for that query. For the above query -fixed_comp_fund_code = 'DMFS' - View1 will have 1 record & view 2 have 0 records. If I pass fixed_comp_fund_code = '00880' , I get data from View2 & not from View1 I am using UNION to combine these 2 Views . Requirement: I cannot sent individual queries to my union view. I want to send a single query Example: institutional_fund_indicative_data institutional_fund_indicative_data_filter = ' {10044} eq ''2000'' AND {711} eq ''DMFS'' AND {15521} eq ''20151231''' and institutional_fund_indicative_data_select = '{1172} as investment_capability,{15891} as related_account_name' and institutional_fund_indicative_data_hostname = 'idwapi' and v_as_of_date = '2015-12-31' and fixed_comp_fund_code = 'DMFS' For the above union query - I am expecting data from View 2 - institutional_fund_indicative_data- It is working as expected. ------------------------------------------------------------------------------------------------------------------------------------------ institutional_fund_indicative_data institutional_fund_indicative_data_filter = ' {10044} eq ''2000'' AND {711} eq ''00880'' AND {15521} eq ''20151231''' and institutional_fund_indicative_data_select = '{1172} as investment_capability,{15891} as related_account_name' and institutional_fund_indicative_data_hostname = 'idwapi' and v_as_of_date = '2015-12-31' and fixed_comp_fund_code = '00880' For the above union query - I am expecting data from View1 = comp_fund_details_prp. But I am getting 0 results even there is a data in View 2. Union is filtering the result What I am expecting is - Denodo to execute the individual queries against their respective base views and produce the result . we want to create a unified view which goes and queries multiple databases using different select/filter/where clauses and resultset will be found in only one db which need to be returned thru joint selection view.
user
08-04-2016 12:00:13 -0400

2 Answers

Hi! Have you considered utilizing view parameters as ‘dummy’ fields in comp_fund_details_prp? If I understand your scenario correctly, what you are experiencing is the expected behavior. You seem to have switched view 1 and 2 midway through your description so let’s take a step back. You have a view C, which is a UNION of views A and B. The fields in view B that don’t exist in view A will have NULL values in view C for the tuples that originate from A. The opposite of this is also true. Therefore, if you execute a query against view C using a where condition that provides a non-NULL value for a column that does not exist in view A, you will not receive any tuples derived from view A as these will not meet the WHERE condition. This is the case in the scenario you are describing as institutional_fund_indicative_data_filter, institutional_fund_indicative_data_select, institutional_fund_indicative_data_hostname are not common columns and will have NULL values in the union view for the tuple you want to be returned. View parameters would alleviate this issue as it would enable to have these fields be common between views A and B. The logic would work like this: 1. View A must be a derived view. If it is a base view, you can build a projection view on top of it to act as an intermediate view. 2. Add one view parameter to your view A for each runtime parameter you have in view B. For example, if you have an institutional_fund_indicative_data_filter interpolation variable in view B you would want to create an institutional_fund_indicative_data_filter view parameter in view A with no default value. 3. In view C, associate your interpolation variables in view B with the respective view parameters in view A. Now the columns will be common between the two underlying views. When you add a WHERE condition specifying a value for your interpolation variables, you are also specifying a value for your view parameters. Thus view C will no longer have NULL values in these particular fields for the tuples derived from view A. I’d recommend taking a look at the ‘Creating Union Views’ section of the VDP Admin guide for more information about how UNION views work and to get a better understanding of why you’re receiving your observed behavior. Hope that helps!
Denodo Team
11-04-2016 16:24:13 -0400
Hi, I’ve thought of another solution that could help you with your situation. If you are using Denodo 6.0 or a recent update of Denodo 5.5 (20150319 or newer), view parameters from underlying views will be used for searching only, but not for filtering when queried in a UNION view. This is the functionality you want, but it only works in situations when the column you are specifying in the WHERE condition is a view parameter, not an interpolation variable as is the case in your situation. However, this provides a new solution. To implement this, consider views A, B, and C as described in my previous comment: 1. Build a derived view on top of View B. 2. Add a view parameter to this derived view for each interpolation variable you have in View B. In the WHERE conditions window, set each interpolation variable equal to each respective view parameter. 3. Create View C as union between this derived view and View A. At runtime, you will be asked to provide values for your view parameters rather than your interpolation variables. The values you provide will be used for searching, but not for filtering. Thus the tuples from View A will not be filtered out. Hope that helps!
Denodo Team
13-04-2016 21:00:45 -0400
You must sign in to add an answer. If you do not have an account, you can register here