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:QuoteSelect 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:
Quoteselect
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
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
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
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.