Author Topic: Union is dropping data from 1 query  (Read 550 times)

Offline bcksr

  • Associate
  • **
  • Join Date: Jan 2018
  • Posts: 1
  • Forum Citizenship: +0/-0
Union is dropping data from 1 query
« 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.


Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,684
  • Forum Citizenship: +340/-1
Re: Union is dropping data from 1 query
« Reply #1 on: 24 Jan 2018 02:17:35 am »
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.

 


       
Twittear