Over (Partition by) usage in FM

[PRIT AMRIT]

Hi Guru's

Would like to understand when Cognos writes a SQL Like below?

sum(T0.C2) over (partition by T0.C0) WE_CBPA_CNT

I have a SQL:

Select CBPA.WE_CBPA_CODE,TIME.DAY_POT_START,SUM(CBPA.WE_CBPA_CNT)
FROM WE_CSTM_CBPA CBPA,TDS_WE_ISELL_ACT_SLS_VOL VOL,MAP_TIME TIME
WHERE CBPA.OBJECT_ID = VOL.WE_CBPA_SHIP_TO_PARENT_OID
AND TIME.DAY_OID = VOL.WE_DAY_OID
AND CBPA.WE_CBPA_CODE='de0029181658'
and CBPA.WE_CBPA_IN_USE_FLAG <>'d'
and VOL.IN_USE_FLAG <>'d'
GROUP BY DAY_POT_START,CBPA.WE_CBPA_CODE

When I impliment the same in Cognos, it writes the below SQL:

select
T0.C0 WE_CBPA_CODE,
T0.C1 DAY_POT_START,
sum(T0.C2) over (partition by T0.C0) WE_CBPA_CNT
from
( select WE_CSTM_CBPA.WE_CBPA_CODE C0,
MAP_TIME.DAY_POT_START C1, sum(WE_CSTM_CBPA.WE_CBPA_CNT) C2
rom
WE_CSTM_CBPA WE_CSTM_CBPA,
MAP_TIME MAP_TIME,
TDS_WE_ISELL_ACT_SLS_VOL TDS_WE_ISELL_ACT_SLS_VOL
where WE_CSTM_CBPA.WE_CBPA_CODE=N'de0029181658'
and TDS_WE_ISELL_ACT_SLS_VOL.WE_CBPA_SHIP_TO_PARENT_OID=WE_CSTM_CBPA.OBJECT_ID
and TDS_WE_ISELL_ACT_SLS_VOL.WE_DAY_OID=MAP_TIME.DAY_OID
group by WE_CSTM_CBPA.WE_CBPA_CODE, MAP_TIME.DAY_POT_START) T0

Have attached the doc with detailed information. Please help me to understand why Cognos use Over(Partition by) cluse and how to overcome the same? Thanks

[CoolP]

See if this helps.

http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/report-studio-generating-sql-with-sumxxxxxx-over-partition-by-yyyyy-zzzzz-4706686

[PRIT AMRIT]

Hi CoolP,

The Link demonstrate a solution for Report Studio. However, I could do so as well.

But the problem is I am using Query Studio and I don't think in Query Studio has Auto summerize option.
In FM i have already set the data item aggregate property 'SUM'

Any other thoughts? Thanks

Cheers,
Prit

[tjohnson3050]

Can you post the relationships in FM between the three query subjects (1.n, 1.1 ect.)?

Also list any determinants defined on the three query subjects.