How to modify this complex join in OBIEE Answers?

Abed Jaji

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

ID	Entity
1	CT
2	VA
3	PT
6	Null
7	GA

Table B

ID
1
2
3
4
5
6

Result needed:

ID	Entity
1	CT
2	VA
3	PT
4	Null
5	Null

[Deleted User]

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!

Abed Jaji

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