1

Am doing a full outer join on 2 tables A and B on column "id" in Cognos report studio. Both tables have multiple records for id column. My requirement is I have to count number of records from each table and show it on a graph. But when I count records, it is multiplying the records and resulting in cartesian product. 

A.id ----   B.id  
1 ------ 1  
2 ------ 2  
2 ------ 2  
3 ------ 4  
4 ------ 5  
5 ------ 6

When I do a count this is what I get: 

A.id ----   B.id ----  Count(A.id)----   Count(B.id)  
1  ----     1   ----     1    ----       1  
2 ----      2  ----      4  ----         4    (Am expecting 2 for these kind of records)  
3  ----      null ---- 1 ---- null  
4 ----      4 ----       1----           1          
5 ----      5  ----      1 ----          1   
null ----        6 ----  null  ----               1

I need to present the total number of records from table A and from Table B in a graph. Since this is resulting in cross product, graph values are not giving correct results.Can any one suggest how to avoid this cartesian product for the 2nd record? Please suggest if this is possible or not?

Andrew
  • 8,445
  • 3
  • 28
  • 46
Poornima Gogi
  • 23
  • 6
  • Can you clarify the problem? What does the second table represent? Is that the derived query from the join? Is Auto-group and Summarize enabled for the queries? – Johnsonium Jul 20 '15 at 16:41
  • Thank you John for looking into this. Auto-group and summarize are set to Automatic. 2nd table is the result after join. After the join, am counting records from each table. If there are 2 records in table A with same id and 4 records in table B with same id, am getting 8 records after the join, which means it is performing Cartesian product. I should be getting count as 2 for table A and 4 for Table B. Is this because I have duplicates and there is no unique column in the query? – Poornima Gogi Jul 20 '15 at 22:58
  • Are you getting a warning about a cross product? Did you define a join between the 2 query subjects? – Andrew Jul 21 '15 at 01:58
  • Andrew, am not getting any warning messages abt cross product. Just the output is not correct. Yes i have defined full outer join between the query subjects as 0-n and 0-n. – Poornima Gogi Jul 21 '15 at 15:31

2 Answers2

0

If you have to display the count separately then use Union of the two tables and count id separately.

Mohsin Shiraz
  • 85
  • 1
  • 6
0

Since you are joining on a non-unique column you will end up with each instance of a particular value on the left side matching up with every instance of that value on the right side.

Imagine if table A looked like this:

enter image description here

...and table B looked like this:

enter image description here

A full outer join of the two tables on ID would result in this:

enter image description here

All combinations have to be represented. This is why your count is showing 4 for each count. There are four rows resulting before auto-group and summarize.

Aggregates added to a query derived from a join can only be calculated on the resulting rows after the join has taken place. In a way, the details of the individual queries are obscured and thus unavailable to be counted etc.

If you generate the SQL on a join like this you will often see that Cognos has done this:

SELECT a.column1, a.column2, b.column1. b.column2 
FROM (
    SELECT column1, column2 
    FROM tableA
    ) a FULL OUTER JOIN
    (
    SELECT column1, column2
    FROM tableB
    ) b ON a.column1 = b.column1

A count in the higher-level query would be added to the outer select but the outer select has no way of knowing how many rows came from A and how many came from B. It can only count the resulting rows of the join.

The only solution I can think of is to do your counts at the individual query level and then pull them up into the joined query.

Johnsonium
  • 2,005
  • 1
  • 13
  • 15