Sigurdur Hergeirsson
asked on
Using character type for MODEL dimension causes rules using CV() to repeat results - why ?
I am using MODEL clause to calculate balance and compound interest.
Using generated date converted to YYYYMM as key to dimension I get balance always repeated each 12 months.
Can someone explain why ?
By using the generated seqence number directly as the dimension-key fixed the problem.
Simplified sql of both cases:
--Here balance reapeats every 12 months
SELECT * FROM (
SELECT TO_CHAR( ADD_MONTHS(DATE '2000-01-01', SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual connect by level <= 100)
ORDER BY YEARMON
)
MODEL
dimension by (YEARMON)
measures (BASE, BAL)
RULES (
BAL[ANY] ORDER BY YEARMON = nvl(BASE[CV()],0) + nvl(BAL[CV()-1],0)
) ORDER BY YEARMON
--Here balance get increased until lastrow (as disired)
SELECT * FROM (
SELECT SEQNR, TO_CHAR( ADD_MONTHS(DATE '2000-01-01', SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual connect by level <= 100)
ORDER BY SEQNR
)
MODEL
dimension by (SEQNR)
measures (YEARMON,BASE, BAL)
RULES (
BAL[ANY] ORDER BY SEQNR = nvl(BASE[CV()],0) + nvl(BAL[CV()-1],0)
) ORDER BY YEARMON
Using generated date converted to YYYYMM as key to dimension I get balance always repeated each 12 months.
Can someone explain why ?
By using the generated seqence number directly as the dimension-key fixed the problem.
Simplified sql of both cases:
--Here balance reapeats every 12 months
SELECT * FROM (
SELECT TO_CHAR( ADD_MONTHS(DATE '2000-01-01', SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual connect by level <= 100)
ORDER BY YEARMON
)
MODEL
dimension by (YEARMON)
measures (BASE, BAL)
RULES (
BAL[ANY] ORDER BY YEARMON = nvl(BASE[CV()],0) + nvl(BAL[CV()-1],0)
) ORDER BY YEARMON
--Here balance get increased until lastrow (as disired)
SELECT * FROM (
SELECT SEQNR, TO_CHAR( ADD_MONTHS(DATE '2000-01-01', SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual connect by level <= 100)
ORDER BY SEQNR
)
MODEL
dimension by (SEQNR)
measures (YEARMON,BASE, BAL)
RULES (
BAL[ANY] ORDER BY SEQNR = nvl(BASE[CV()],0) + nvl(BAL[CV()-1],0)
) ORDER BY YEARMON
Seems that the model recognizes the YEARMON dimension as being date based
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.