SAP Universe Complex Conditional Joins

[former_member672670]

Hi All,

I am trying to implement the following two complex joins in the universe (data foundation) for some Webi reports. Each join involves 3 tables as shown below.

1. Inner Join Table1 ON Coalesce(Table2.ID, Table3.ID) = Table1.ID OR Table3.ID = Table1.ID
2. Left Join Table1 ON Table1.ID = Table2.ID AND Table3.ID > Table1.ID

I entered the second join condition in the SELECT for the join between Table1 and Table2. It resulted in a database error in the Webi report saying it could not bound the second condition. Also, the join lines between the tables in the data foundation seemed strange with an open ended line in the middle.

Any ideas on what would be the best way to implement this in the information design tool keeping performance in mind?

[Joe_Peters]

That doesn't make sense at all. Why would you have two different joins between the same three tables in the same query?

The two OR'd parts of the first join are redundant. And the second part of the second join (table3.id > table1.id) is incompatible with the second part of the first join (table3.id = table1.id).

[former_member672670]

Sorry, I think I forgot to add the specific ids for the tables which is creating the confusion. I have updated the ids for the first join below. The second join has been resolved by using a field from Table1 instead of Table3.

Inner Join Table1 ON Coalesce(Table2.Id1, Table3.Id2) = Table1.Id3 OR Table3.Id4 = Table1.Id3

For the above join, do you know if it is it possible to do a CASE statement in the join expression to account for the multiple joins based on the Null condition?