Hello - I am creating a union on two queries that are each created by a join. When I run each query itself my data is accurate. When I create the union, I get the correct total count but it is dropping data from one of the queries. All columns are the same in each? I'm very new to Cognos and am quite stumped on this.
EX:
Q1 join Q2 to create Q3
Q4 join Q3 to create Q5
Q6 join Q3 to create Q7
Union Q7 & Q5 to create final Q
hope that makes sense... when I run Final Q - I am dropping specifically the data from Q3 that is in Q7. But when running Q7 independently it is correct.
Quote from: bcksr on 23 Jan 2018 11:07:46 PM
Hello - I am creating a union on two queries that are each created by a join. When I run each query itself my data is accurate. When I create the union, I get the correct total count but it is dropping data from one of the queries. All columns are the same in each? I'm very new to Cognos and am quite stumped on this.
EX:
Q1 join Q2 to create Q3
Q4 join Q3 to create Q5
Q6 join Q3 to create Q7
Union Q7 & Q5 to create final Q
hope that makes sense... when I run Final Q - I am dropping specifically the data from Q3 that is in Q7. But when running Q7 independently it is correct.
There is a property called "Duplicates" that allows you to select Remove or Preserve. Could this be causing you the problem? If you know SQL it is the difference between UNION and UNION ALL. If there is a row in the Q7 result set that is identical to a row in the Q5 result set then this setting will determine whether both rows are returned in the final result set or not.
also check your query property... Auto Group & Summarize... set accordingly