Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
A filter on an outer joined table changes to Equi Join.
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.
Answers
-
Dear Manoj,
Yes it will. and it is obvious when we apply a filter for the right side in left outer join, it is no longer an outer join. You need to apply the filter before you join. if it is a smaller table create a view in RPD and filter the column in view itself. If it is required dynamically you can pass a request variable to the view query. Then make the join.,
0 -
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.
0 -
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.
0 -
... I know why my answer is incorrect......
0