A filter on an outer joined table changes to Equi Join.

Question

Hello,

I am using OBI EE 12.2.1.2.0.

Schema : Scott.

Tables:

Emp (EmpNo [Primary Key], EmpName, etc.),

EmpBonus: (EmpNo [Foreign Key], BonusReceived, BonusType, ISRowDeleted).

Emp table contains 14 rows while EmpBonus contains 4 rows, 2 of them are soft-deleted.

I have defined a Left Outer Join on EmpBonus. The join works fine.

However, I am not able to make the BI Server to generate a SQL Query similar/like (not exactly as under) the below one (the objective is to filter on IsRowDeleted column).

select * from emp e, empbonus eb

where eb.empno  = e.empno

and eb.isrowdeleted  = 'N'

order by 1;

OBI EE is generating the below SQL Query.

WITH SAWITH0 AS (select distinct T7715.BONUS_RECEIVED as c1,  T7715.BONUS_TYPE as c2,  T7338.EMPNO as c3,  T7338.ENAME as c4from   EMP T7338 /* DIM_EMP_DF */ left outer join EMP_BONUS T7715 /* DIM_EMP_BONUS_OTR */ On T7338.EMPNO = T7715.EMPNOwhere ( T7715.IS_ROW_DELETED = 'N' ) )select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select 0 as c1,  D1.c1 as c2,  D1.c2 as c3,  D1.c3 as c4,  D1.c4 as c5from   SAWITH0 D1order by c4, c5, c3, c2 ) D1 where rownum <= 65001

And the results displayed are as under.

What could be the reason that OBI EE is not able to apply the proper filter on "Bonus Record Deleted Indicator" presentation column? Is there any way where we can change the WHERE to AND as per the following screenshot?

Thank you.

Regards,

Manoj.

asim cholas · Answer

... I know why my answer is incorrect......

Manoj Dixit · Answer

Hello Robert,

I added it to the complex join. And now the filter "is_row_deleted = 'N'" is added using AND instead of WHERE.

Asim, thanks for your reply as well.

Regards,

Manoj.

Robert Angel · Answer

In conjunction with Asim's comments note you can also filter the table in the physical layer, or create an opaque view as he alludes.

This is only if the condition always applies, otherwise you need to add additional variable based logic to achieve this, in a similar fashion, though not in the same place, you would use to impose row level security.