Preserve Left outer join but narrow results

[cschnu]

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:

Code Select Expand


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:

Code Select Expand


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?

[Arsenal]

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

[cschnu]

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?