Full outer join in query from same table

[Former Member]

Dear Experts

We have a table with sales_metric_key, sales_amount and other dimensional columns. Now, created some new objects with respect to sales amount on condition with sales metric key. For instance,

Object: POS Sale Amount (measure)

Select sum(sales_amount) from SALES_TABLE

Where sales_metric_key =1

Object: Store Sale Amount (measure)

Select sum(sales_amount) from SALES_TABLE

Where sales_metric_key =2

Now when these two objects were pulled together with other dimensional objects, we get the result in multiple path or union query as I understand that its normal behavior when 2 different conditions were used on same column. (see below picture).

Is it possible to achieve in single query when 2 measure objects were used? Like given below,

Select sum(sales_amount) from SALES_TABLE

Where sales_metric_key =1 and sales_metric_key =2

I remember such instance were handled in BO 3.1 universe with its parameters but I not sure how it was done,

Tried using parameters functions on business layer (enabling ANSI92 and others given in picture below).

Also with disabling and enabling multiple SQL statement for each measure and multiple SQL statement for each context but neither have helped.

Can anyone provide suggestion on this requirement?

SAP Business Objects 4.0 SP6 is used and oracle 11g is the database.

------

Regards

Kishore Balachandran

[amitrathi239]

Hi,

All these objects are based on the derived table?

I think this is happening because where clause is different for each measure object.If where clause is the same for both then  both will come in the single SQL.

Amit

[Former Member]

Hi Amit

No, it is not a derived table.

Just for the test case, I Pulled a table and created objects using its columns available. I understand that same condition would give me the required result but on old universe (few years back for different client I worked with) created in BO 3.1 used the same method and achieved the query result shown on my post.

--

Kishore Balachandran

[amitrathi239]

Hi,

I am not very sure but whenever you will use different where clause for both measure then it will generate two sqls.

How you will do in Oracle if you have to do this.Can you do in the oracle with one Select,From,Where clause for both sales_amount without using subquery?

Object: POS Sale Amount (measure)

Select sum(sales_amount) from SALES_TABLE

Where sales_metric_key =1

Object: Store Sale Amount (measure)

Select sum(sales_amount) from SALES_TABLE

Where sales_metric_key =2

Amit

[Former Member]

Hi Amit

I observed the same technique used in one our test universe. I have attached the following.

As you can see they have produced the result in different manner. My question is, how it can be achieved?

---

Kishore Balachandran

[Former Member]

Hi Kishore,

If you combine the query into a single one, you will not get any data. Because, in a particular row, the metric key cannot be both 28 and 29.

for example the source record is

metric key measure

28               100

29                200

If you filter metric key as 28 and 29 then either of the rows will satisfy the filter condition and will not retrieve any data.

So i am not understanding what is the actual requirement.

Because of this reason only experts suggests not to use any where condition in the object definition (as a best practice to avoid these kind of situations)