How to use "OR" Condition in Physical Joins?

Nikhila reddy

Member_D

Member number	Member name	Contact _id
100	Nikhila	900
200	Karthik	901
300	Manisha	902

Contact_D

Contact_number	Contact_name
900	Nikhila
902	Karthik
905	Manisha
906	Vinisha

Audit_F

Record I'd	Column name	Old value	New value
100	Member column	A	B
900	Contact column	C	D
200	Member column	A	B
905	Contact column	A	C
906	Contact column	B	D

Audit_F.Record Id Stores Contact and Member Id’s respective to column name, if it is contact column then it will store Contact Id, and if column name is member Column then Record Id will store Member Number

We we do this joins in DB level we are getting expected results.

Member_D.member_Id =audit_F.record I'd  or member_D.contact_id=audit_F.record I'd

And  contact_D.contact I'd =audit_F.record_id

Here  audit.Record I'd is storing member and contact it's

Our requirement is to get the data of contact and member and audit data in one report

But in analytic this is not satisfying

Expected result:

Member number	Contact number	Column name	Contact name	Member name
100	Member column	900	Nikhla	Nikhila
100	Contact columnn	900	Nikhila	Nikhila
200	Member column	901	Karthik	Karthik
	Contact column	905	Manisha
	Contact column	906	Vinisha

Result which analytics giving

Member number	Contact number	Column name	Contact name	Member name
100	Member column	900		Nikhila
	Contact column	900	Nikhila
200	Member column	901		Karthik
	Contact column	905	Manisha
	Contact column	906	Vinisha

Here contact number 900 is also a member so we are unable to see his member details ,

As we can't do 'OR' condition in rpd . So please suggest me to get member details if  he is member.

Gianni Ceresa

Nikhila reddy wrote:As we can't do 'OR' condition in rpd . So please suggest me to get member details if he is member.

You can definitely do a OR condition in the RPD, just open the formula editor for that so you write your condition correctly. If you just pick columns from the 2 lists OBIEE create a physical foreign key which just do AND, if you instead open the expression builder it becomes a physical complex join where you can write the kind of condition you want.

Nikhila reddy

Cant we use one column for more than one time

Gianni Ceresa

What's your OBIEE version?

Just did that in my RPD and it didn't complain about "column used more than once".

Nikhila reddy

Nikhila reddy

After adding this OR Condition in this Expression and save, automatically it is changing it to AND,

Can You help me with this please

Gianni Ceresa

I don't have a old 11.1.1.7 available now ....

More in general, looking back at your example : there are mistake in posted example as it's kind of hard to tell which columns means what and come from where as it's same/similar values and you mixed columns names a bit.

But you can't do it with a single join and a OR, because it will never put rows one next to each other, that's just how SQL works.

To get what you look for you will need to uses aliases to have the same table used multiple times one next to each other so you can all the columns on the same row.

Using aliases you will automatically solve the "OR" issue as you will not have any OR anymore.

[Deleted User]

You're probably just making the same error most people with these problems do: not starting from scratch with a blank join which will then become a complex join because all your screenshots say "physical join"...which is wrong.

tl;dr...it works: