COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: cschnu on 24 Jul 2012 11:02:35 AM

Title: Preserve Left outer join but narrow results
Post by: cschnu on 24 Jul 2012 11:02:35 AM
Is there a way in Cognos framework manager to preserve the rows in table a and still filter on table b?  In terms of SQL standard you would have to but the filter condition in the left outer join itself so for example take the following:


select count(a.id) from table a lefter outer join table b on b.id = a.id where b.value = 1

The above code will of course filter the entire query for b.value = 1, which in most cases is what you would want but in a few isolated cases we would like to include all rows from table a and all rows from table b where value = 1.  So in SQL you would do the following to achieve that:


select count(a.id) from table a lefter outer join table b on b.id = a.id and b.value = 1


So the above code includes only those records from table b that have the value of 1 but includes all records from table a. Is there any way to build this into framework manager or change this behavior in report studio without manually joining tables?
Title: Re: Preserve Left outer join but narrow results
Post by: Arsenal on 26 Jul 2012 11:49:33 AM
How about embedding a filter in the Query Subject of b to value = 1. This will ensure that always only those rows are returned from b whose value=1. Essentially, it's like specifiying a Where clause in b's select *

u can leave the left outer join as is so when you are selecting from both the tables, the left outer join and the where clause from above will kick in together
Title: Re: Preserve Left outer join but narrow results
Post by: cschnu on 31 Jul 2012 08:36:34 AM
Quote from: Arsenal on 26 Jul 2012 11:49:33 AM
How about embedding a filter in the Query Subject of b to value = 1. This will ensure that always only those rows are returned from b whose value=1. Essentially, it's like specifiying a Where clause in b's select *

u can leave the left outer join as is so when you are selecting from both the tables, the left outer join and the where clause from above will kick in together

So that will only work if you always want to filter the left table by a certain field?