Skip to Content
0

Eliminate Full outer join in Universe Query

Oct 19, 2016 at 08:21 PM

114

avatar image
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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Oct 20, 2016 at 08:20 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded