Hi Techies,
I am getting wrong results from Cognos Generated SQL.
We are using 2 transactions tables to join, and few other Dimension Tables.. Because of Sum Over Partition, We are getting aggregated value for each row.
Kindly help me to resolve this issue.
Cognos Generated SQL --
SELECT "T0"."C0" "No__of_Records",
"T0"."C1" "Account_Category",
"T0"."C2" "Currency_Description",
"T0"."C3" "Reversal_Date",
SUM("T0"."C4") over (partition BY "T0"."C2") "Credit_Amount",
"T0"."C5" "Payment_Activity_Type"
FROM
(SELECT COUNT("Payment"."PAY_ID") "C0",
"Customer"."ACCT_CTGY" "C1",
"Account_Currency"."CRCY_ID" "C2",
"Payment_Activity"."RVRS_DATE" "C3",
SUM("Payment"."CRDT_AMNT") "C4",
"Payment_Activity"."PAY_ACTV_TYPE" "C5"
FROM "ODS_ST_VIEWS_LVT"."PAY" "Payment",
"ODS_ST_VIEWS_LVT"."CUST" "Customer",
"ODS_ST_VIEWS_LVT"."CRCY" "Account_Currency",
"ODS_ST_VIEWS_LVT"."PAY_ACTV" "Payment_Activity",
"ODS_ST_VIEWS_LVT"."ACCT" "Account"
WHERE "Account"."ACCT_KEY" ="Payment"."ACCT_KEY"
AND "Account_Currency"."CRCY_KEY"="Account"."CRCY_KEY"
AND "Payment"."CRDT_ID" ="Payment_Activity"."CRDT_ID"
AND "Customer"."CUST_KEY" ="Payment_Activity"."CUST_KEY"
GROUP BY "Customer"."ACCT_CTGY",
"Account_Currency"."CRCY_ID",
"Payment_Activity"."RVRS_DATE",
"Payment_Activity"."PAY_ACTV_TYPE"
) "T0"
Why Cognos is calculating Sum again with Sum Over Partition in outer query. Actually Outer Query is not required. What changes do I need to do in FM regarding cardinality (if required). Output is attached.
Thanks in Advance
Regards
Saikrishna Mamidi