Guys ,
am trying to achieve this ..
SUM ( (E_373)) OVER (PARTITION BY E103195, (NP105)) AS NP104,
Need help where am trying to use
EVALUATE_AGGR('SUM (%1) OVER (PARTITION BY %2,%3)', "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" )
When i use the simple logical query like ..
Sum("Fact - SCM AP Spend Summary"."Converted Amount" by "GL Period"."Period Year" ,"SCM Vendor Mapping"."Revised Vendor Name").
The result comes in no time but the problem is until i use this "SCM Vendor Mapping"."Revised Vendor Name" in the query i get unwanted results ... and i dont want to add this column in the query . Now there are other calculated column based on "Revised Vendor" ..
So i thought of using Evalaute Aggr but the query adds a full outer join at the bottom and then it keeps running for ever.
WITH SAWITH0
AS ( SELECT SUM (T355113.CONVERTED_AMT) AS c1,
T296961.PERIOD_YEAR AS c2,
T355156.REVISED_VENDOR_NAME AS c3
FROM SCM_ACCOUNT_GROUP T353880 /* Dim_SCM_ACCOUNT_GROUP */
,
ORGANIZATIONS T296966 /* Dim_ORGANIZATIONS */
,
R3_GL_PERIOD T296961 /* Dim_R3_GL_PERIOD_GL_Period */
,
R3_AP_SPEND_SUMMARY T355113 /* Fact_R3_AP_SPEND_SUMMARY */
,
SCM_VENDOR_MAP T355156 /* Dim_SCM_VENDOR_MAP */
WHERE ( T296966.ORG_ID NOT IN (5)
AND T353880.IFIS_GROUP NOT IN ('Out of Scope')
AND T355113.ORG_ID NOT IN (5)
AND T296961.PERIOD_NAME = T355113.PERIOD_NAME
AND T296966.ORG_ID = T355113.ORG_ID
AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID
AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'
AND T355113.V3_VENDOR_ID = T355156.V3_VENDOR_ID
AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))
AND T353880.CATEGORY IS NOT NULL)
GROUP BY T296961.PERIOD_YEAR, T355156.REVISED_VENDOR_NAME),
SAWITH1
AS (SELECT SUM (T355113.CONVERTED_AMT) AS c1
FROM SCM_ACCOUNT_GROUP T353880 /* Dim_SCM_ACCOUNT_GROUP */
,
ORGANIZATIONS T296966 /* Dim_ORGANIZATIONS */
,
R3_GL_PERIOD T296961 /* Dim_R3_GL_PERIOD_GL_Period */
,
R3_AP_SPEND_SUMMARY T355113 /* Fact_R3_AP_SPEND_SUMMARY */
WHERE ( T296966.ORG_ID NOT IN (5)
AND T353880.IFIS_GROUP NOT IN ('Out of Scope')
AND T355113.ORG_ID NOT IN (5)
AND T296961.PERIOD_NAME = T355113.PERIOD_NAME
AND T296966.ORG_ID = T355113.ORG_ID
AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'
AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID
AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))
AND T353880.CATEGORY IS NOT NULL)),
SAWITH2
AS ( SELECT SUM (D2.c1) AS c1,
D1.c2 AS c2,
D1.c3 AS c3,
1 AS c4
FROM SAWITH0 D1, SAWITH1 D2
GROUP BY D1.c2, D1.c3),
SAWITH3
AS ( SELECT SUM (T355113.CONVERTED_AMT) AS c1,
T296961.PERIOD_YEAR AS c2,
T355156.REVISED_VENDOR_NAME AS c3
FROM SCM_ACCOUNT_GROUP T353880 /* Dim_SCM_ACCOUNT_GROUP */
,
ORGANIZATIONS T296966 /* Dim_ORGANIZATIONS */
,
R3_GL_PERIOD T296961 /* Dim_R3_GL_PERIOD_GL_Period */
,
R3_AP_SPEND_SUMMARY T355113 /* Fact_R3_AP_SPEND_SUMMARY */
,
SCM_VENDOR_MAP T355156 /* Dim_SCM_VENDOR_MAP */
WHERE ( T296966.ORG_ID NOT IN (5)
AND T353880.IFIS_GROUP NOT IN ('Out of Scope')
AND T355113.ORG_ID NOT IN (5)
AND T296961.PERIOD_NAME = T355113.PERIOD_NAME
AND T296966.ORG_ID = T355113.ORG_ID
AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID
AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'
AND T355113.V3_VENDOR_ID = T355156.V3_VENDOR_ID
AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))
AND T353880.CATEGORY IS NOT NULL)
GROUP BY T296961.PERIOD_YEAR, T355156.REVISED_VENDOR_NAME),
SAWITH4
AS (SELECT SUM (T355113.CONVERTED_AMT) AS c1
FROM SCM_ACCOUNT_GROUP T353880 /* Dim_SCM_ACCOUNT_GROUP */
,
ORGANIZATIONS T296966 /* Dim_ORGANIZATIONS */
,
R3_GL_PERIOD T296961 /* Dim_R3_GL_PERIOD_GL_Period */
,
R3_AP_SPEND_SUMMARY T355113 /* Fact_R3_AP_SPEND_SUMMARY */
WHERE ( T296966.ORG_ID NOT IN (5)
AND T353880.IFIS_GROUP NOT IN ('Out of Scope')
AND T355113.ORG_ID NOT IN (5)
AND T296961.PERIOD_NAME = T355113.PERIOD_NAME
AND T296966.ORG_ID = T355113.ORG_ID
AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'
AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID
AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))
AND T353880.CATEGORY IS NOT NULL)),
SAWITH5
AS (SELECT SUM (D2.c1) OVER (PARTITION BY D1.c2, D1.c3) AS c4, 1 AS c5
FROM SAWITH3 D1, SAWITH4 D2)
SELECT D1.c1 AS c1, D1.c2 AS c2, D1.c3 AS c3
FROM (SELECT DISTINCT 0 AS c1, D2.c4 AS c2, D1.c1 AS c3
FROM SAWITH2 D1 FULL OUTER JOIN SAWITH5 D2 ON D1.c4 = D2.c5) D1
WHERE ROWNUM <= 5000001..
Any thoughts Please help !!