Distinct is not applying in 11.1.1.9 .Same worked in 11.1.1.7

Question

Hi,

catalog and rpd for both version is same. There is no change in that between 11.1.1.9 and 11.1.1.7. But in one of my dashboard my query is getting different. Any idea why it is behaving like this.

11.1.1.7

SAWITH1 AS (select  /*+ USE_HASH(T16442) */  distinct sum(T11605.TOTAL_LIMITS) as c1,

T21978.PERIOD_NAME as c2,

T21978.PERIOD_ID as c3,

T19616.CLIENT_SUBSEGMENT as c4,

T16442.CLIND_CLNT_INDUSTRY_SUBGROUP as c5,

T16442.LEIND_CLNT_INDUSTRY_SECTOR as c6,

T16418.BL_LOCATION as c7

11.1.1.9

SAWITH1 AS (select  /*+ USE_HASH(T16442) */  sum(T11605.TOTAL_LIMITS) as c1,

T21978.PERIOD_NAME as c2,

T21978.PERIOD_ID as c3,

T16442.LEIND_CLNT_INDUSTRY_SECTOR as c4,

T16442.CLIND_CLNT_INDUSTRY_SUBGROUP as c5,

T16442.CLIND_CLIENT_INDUSTRY_GROUP as c6,

T16418.BL_LOCATION as c7,

T19616.CLIENT_SUBSEGMENT as c8

Gianni Ceresa · Answer

3516041 wrote:But in one of my dashboard my query is getting different. Any idea why it is behaving like this.

Yes, the idea is quite simple: 11.1.1.7 vs 11.1.1.9

11.1.1.9 changed various things compared to 11.1.1.7, therefore the queries can also be generated in a different way. It's part of the activities of an upgrade to test everything and adapt where needed.

A different query can be the result of a not idea model, and OBIEE try to guess what it means. Different versions, different guesses.

It all depends on what your RPD is like, and what your analysis is asking for, so no way to give you a more precise question without those pieces.