-
cannot create a fast refresh materialized view from a complex query
Hi All,
If I add this part AND TO_CHAR(A.CAL_DAY_DT, 'YYYYMM') <= TO_CHAR(TRUNC(SYSDATE), 'YYYYMM') Iam getting the follwoing error
cannot create a fast refresh materialized view from a complex query
Cheers!
OraKid.
-
Documented behaviour ... see this documentation
-
Thnx Slimdave.
Then how do I handle this...
I need all the records from day 1 in my dim table till last day of the current month.
eg, first day in my dim table 01-01-2000 and last day of the curent month 30-09-2003.
Cheers!
OraKid.
-
Your MV will have to be based entirely on data in the table(s) without reference to sysdate.
A simple approach would be to include a column that states whether the row is to be included in the MV or not, filte the MV by that column value, and update it peridically (every month, I guess) to generate the incluion of new rows into the MV.
-
Thnx Slimdave. I got my answer by adding another table...
One more help. Is there a substitute for anlytical function LAG(). I mean can we write this is a different way.
'coz I cannot use LAG() function in FAST REFRESH MATERIALIZED VIEWS.
Issue boy...
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID
I need you input on this. This is the script,
CREATE MATERIALIZED VIEW MV_SUB_DELINQUENCY_FLOW_CYCLE
REFRESH FAST ON DEMAND
AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.ACCOUNT_ID,
A.CYCLE_NUMBER,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID,
A.DELINQUENCY_STATUS_ID,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND A.BANKRUPTCY_CHGOFF > 0 THEN
'Y'
ELSE 'N' END) BANKRUPTCY_FLAG,
NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL,
A.ROWID AS AROWID,
B.ROWID AS BROWID,
C.ROWID AS CROWID
FROM CYCLE_SUMMARY_FACT A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.CYCLE_END_DATE = C.CAL_DAY_DT
AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);
Cheers!
OraKid.
-
CAN ANYONE SHARE THEIR IDEA PLSSSS...
One more help. Is there a substitute for anlytical function LAG(). I mean can we write this is a different way.
'coz I cannot use LAG() function in FAST REFRESH MATERIALIZED VIEWS.
Issue boy...
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID
I need you input on this. This is the script,
CREATE MATERIALIZED VIEW MV_SUB_DELINQUENCY_FLOW_CYCLE
REFRESH FAST ON DEMAND
AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.ACCOUNT_ID,
A.CYCLE_NUMBER,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID,
A.DELINQUENCY_STATUS_ID,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND A.BANKRUPTCY_CHGOFF > 0 THEN
'Y'
ELSE 'N' END) BANKRUPTCY_FLAG,
NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL,
A.ROWID AS AROWID,
B.ROWID AS BROWID,
C.ROWID AS CROWID
FROM CYCLE_SUMMARY_FACT A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.CYCLE_END_DATE = C.CAL_DAY_DT
AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|