cannot create a fast refresh materialized view from a complex query

[balajiyes]

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

[slimdave]

Documented behaviour ... see this documentation (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#40960)

[balajiyes]

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.

[slimdave]

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.

[balajiyes]

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);

[balajiyes]

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);