How to modify this complex join in OBIEE Answers?

Question

How do I achieve below join condition for one specific report in OBIEE 12c Answers?

Select * from A right outer join B

on (a.id = b.id and a.entity is not null)

I have defined everything in rpd except 'a.entity is not null'. When I add this condition 'a.entity is not null' in rpd or in Answers to table A, it adds it to the where clause which is not right result because I lose ID 4 and ID 5 records from table B. I don't want to lose them from Table B. Is it possible to achieve this through combining two analysis results using (UNION, INTERSECT or MINUS)? If not, what else can i try?

Table A

IDEntity1CT2VA3PT6Null7GA

Table B

ID123456

Result needed:

IDEntity1CT2VA3PT4Null5Null

Abed Jaji · Answer

Resolve this issue through UNION in Answers.

Select * from A right outer join B

on (a.id = b.id)

where a.entity is null

UNION

Select * from A right outer join B

on (a.id = b.id)

where a.id is null

[Deleted User] · Answer

Forget the query for a moment. What is that in terms of your model? What kind of entity should that represent? A dimension? A fact?

The better you prepare the data, the easier it is to build things and you don't need to bother about them - especially in the front end you should NEVER have to think about this!