cancel
Showing results for 
Search instead for 
Did you mean: 

Why filter on Data Foundation does not work with using Outer Join?

Former Member
0 Kudos

Dear Experts,

I'm facing problem with filtering on Data Foundation tables.

I tried to filter on join and filter column on table too but it didn't work with outer join.

Column I filtered still have all data.

Best regards,

Chenna Yon

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Chenna,

This is normal SQL behavior.  When you use an outer join between tables A and B, you are asking for all the values of a table A whether or not they have a corresponding value in B.  If you then filter B, the filtering will have no effect on table A, and this is by design.

If you want to filter A by B, you need to change the outer join to an inner join.

I suggest you try your query out in SQL to verify the behavior you want before translating it into the Data Foundation.

Answers (1)

Answers (1)

Former Member
0 Kudos

To nuance what I wrote above (and to prove that you need to play around with the SQL it to see it work), there is also a difference depending on the syntax of your query.

When you use ANSI syntax, you have separated the WHERE clause (where your filtering will be applied by BO) from the join condition, so the filtering will be applied no matter what. In the case of an outer join when you're filtering on the outer joined table, this will most likely return empty results. 

If you use the traditional Oracle-specific join syntax (with the outer join expressed with ( + ) ), the filtering applies with along with the outer join, and there isn't any filtering at all and what I said above applied.

To summarize :

Using ANSI syntax:

select * from A

left join B on (A.my_col = B.my_col)

where B.another_col = 'Constant'

This will filter all lines in the results for which there is no line in B, since the outer join does not include the filtering by the constant value and NULL will never satisfy this WHERE clause.

Using Oracle "historical" syntax:

select A.* from A, B

where A.my_col = B.my_col(+)

and B.another_col(+) = 'Constant'

This will not filter any lines by the constant value since it is part of the outer join.

You can control the syntax used by BO at the universe level.