Hi to everybody…i have 2 simple items in bo universe (2 aggregate indicators) , but when i put them together in a query, Bo think they are incompatible and so the result is this in the picture… he make 2 distinct queryes and then he join…but in pl/sql i usally do only a query, and i have the correct result…what i can do in the designer, to telle that the object are compatible, and that he can do it in only one query? Thanks to everybody!!
flavio20002 (BOB member since 2010-03-23)
Hi,
Do both measure come from the same (fact) table? Or from 2 different tables?
Marek Chladny 
(BOB member since 2003-11-27)
They came from 2 different table…for example
select sum(table1.a), sum(table2.b)
from table1,table2
where table1.key=table2.key
becomes in bo
select sum(table1.a)
from table1
join
select sum(table2.b)
from table1,table2
where table1.key=table2.key
flavio20002 (BOB member since 2010-03-23)
Hi,
Your first query
can be correct only when the cardinality of the join between table1 and table2 is 1:1. As soon as it’s 1:N the above query can’t give you a correct result.
So check the universe your report is built on and check the cardinality of the join as well as context defined in the universe. If the tables belong to different contexts then BO splits the query into 2 - one query per each measure.
Marek Chladny (BOB member since 2003-11-27)
The table are in the same contest…and yes, the join is 1:1…but anyway bo split it in 2 queries…
flavio20002 (BOB member since 2010-03-23)
Hi,
Then review the “Multiple SQL statements for each measure” parameter of the universe. You can find it in the menu File -> Parameters -> SQL tab. Clear the check box if it’s checked.
This will, however, affect the generation of queries for all reports that are based on the universe. So you need to be careful about this change.
Marek Chladny (BOB member since 2003-11-27)
If they are 1:1 and the same grain, then you may want to build a derived table to sit them together.
mmm this is useful for another purpouse…si i can’t leave it…i will try to modify the context and look what happen…or i will use the derivated table…thanks to everybody!
flavio20002 (BOB member since 2010-03-23)
I have it! I’ve understood the difference between “Multiple sql statement for each context” and “Multiple sql statement for each measure”.
I just want that the multiple sql is in different contest, not in the same…now it works, without the “Multiple sql statement for each measure”!!
Thanks to everybody
flavio20002 (BOB member since 2010-03-23)
Hi, Marek, can you explain
"select sum(table1.a), sum(table2.b)
from table1,table2
where table1.key=table2.key "
why if those two tables are 1 to 1 to give correct result? can be correct only when the cardinality of the join between table1 and table2 is 1:1. As soon as it’s 1:N the above query can’t give you a correct result. .
I dont quite understand this statement. thanks
lavinaluo (BOB member since 2005-02-14)
Hi,
An example:
Let’s have 2 tables, A and B:
A has only 1 row:
key…amount
1…100
B has 2 rows:
key amount
1…100
1…200
Obviously, the cardinality between A and B is 1:N
The query:
select sum(A.amount) as A_amount,
sum(B.amount) as B_amount
from A, B
where A.key = B.key
will give the result:
A_amount…B_amount
200…300
As you can see, A_amount is not the correct result.
It’s called a fan trap and it needs to be solved in a universe.
Marek Chladny (BOB member since 2003-11-27)