Why BO Creates Two queries???????

[bcauser]

Hi Friends,

We are facing a very interesting problem
Two of the measures available in our universe are
1)sum(decode(V5IN716_V1_alias.MIS_CODE,'40',V8SPM420_V.A9065))
2)SUM(V8SPM420_V.A9060)

When we create a report with dimension (V5IN716_V1_alias.V05605) and the above two measures,
BO creates two independent queries

These queries are.....

Query I

SELECT
V5IN716_V1_alias.V05605,
sum(decode(V5IN716_V1_alias.MIS_CODE,'40',V8SPM420_V.A9065))
FROM
V5IN716_V1 V5IN716_V1_alias,
V8SPM420_V
WHERE
( V5IN716_V1_alias.A6100=V8SPM420_V.A6100 )
GROUP BY
V5IN716_V1_alias.V05605


Query II

SELECT
V5IN716_V1_alias.V05605,
SUM(V8SPM420_V.A9060)
FROM
V5IN716_V1 V5IN716_V1_alias,
V8SPM420_V
WHERE
( V5IN716_V1_alias.A6100=V8SPM420_V.A6100 )
GROUP BY
V5IN716_V1_alias.V05605


We expect BO to create a single query mentioned below...
cos except the measure object everything is same in the query..
There is no question of incompatible objects

The single query should look like this

SELECT
V5IN716_V1_alias.V05605,
sum(decode(V5IN716_V1_alias.MIS_CODE,'40',V8SPM420_V.A9065)),
SUM(V8SPM420_V.A9060)
FROM
V5IN716_V1 V5IN716_V1_alias,
V8SPM420_V
WHERE
( V5IN716_V1_alias.A6100=V8SPM420_V.A6100 )
GROUP BY
V5IN716_V1_alias.V05605



Does anybody know why BO creat two queries????????

Thanks in Advance

 

[GKar]

Hi,

The two SQL generated can come from:

1 - the query needs to use two contexts to run. BO generate 1 SQL for each context.

2 - The option "Multiple SQL statment for each measure" is selected in the universe parameters.
To check that, go to File>>Parameters and SQL tab

3 - Some objects are not compatible


Hope it will help
GKar