Eliminate Full outer join in Universe Query

[Former Member]

I am new to IDT - so this is a "beginner" question and I hope the community doesn't mind. i did take the boid10 course but it was some time ago.

I am working on semantic layer and need to aggregate All Delivery Qty (from Delv Table) and also Delivery Qty where order type = W (from Delv table join to order table to get order type).

The resulting script is a full outer join between the first (All) and second (W) queries.

Is there a way to design the Universe / BL in such a way that that this could be accomplished in one query? (via context/alias/etc)

aka: Select sum(delv.qty), sum(case when order.ordertype = 'W' then delv.qty else 0 end) from delv inner join order on order.id = delv.id)

[amitrathi239]

This is as per product behaviour.If you used any object from universe where condition is mentioned in the universe object then you will get two sql with full outer join in webi SQL. datawise it should be fine.

If you will remove the Where condition in universe object then you will get full outer join in webi sql.