I have a multidimensional model which I am trying to apply a FOR iteration against. However, I need to add a constraint to one of the dimensions in the FOR iteration (e.g. stop the MODEL from iterating across rows for which it shouldn't). Below is an example data set:
START_YEAR DT_YEAR_NUMBER 2019 2018 2019 2019 2020 2019 2020* 2020*
Note that the asterisks represent rows which do not yet exist in the data set and will be generated using the FOR clause.
Basic query is something like this:
WITH START_YEARS AS ( SELECT EXTRACT(YEAR FROM SYSDATE) AS START_YEAR FROM DUAL UNION ALL SELECT EXTRACT(YEAR FROM SYSDATE)+1 AS START_YEAR FROM DUAL ) SELECT * FROM YRS MODEL DIMENSION BY (START_YEAR, DT_YEAR_NUMBER) MEASURES ( TO_NUMBER(NULL) AS SOME_VALUE ) RULES SEQUENTIAL ORDER ( SOME_VALUE[ FOR START_YEAR IN (SELECT START_YEAR FROM START_YEARS),--this works correctly FOR ??? --here's where I'm at a loss ] = 1 )
OKAY, so where I have the question marks above, I am not sure how to constrain the for loop to maintain that I only want to generate data for 2020 in 2020 and not data for 2019 in 2020. In other words:
WHERE DT_YEAR_NUMBER >= START_YEAR
E.g. the data should look like:
START_YEAR DT_YEAR_NUMBER SOME_NUMBER 2019 2018 1 2019 2019 1 2020 2019 1 2020* 2020* 1
The data should not look like:
START_YEAR DT_YEAR_NUMBER SOME_NUMBER 2019 2018 1 2019 2019 1 2019* 2020* 1 2020 2019 1 2020* 2020* 1
I've attempted a where statement, but that doesn't work, I've even tried to apply the where statement to the query, but the model actually overwrites the constraint of the WHERE clause (which makes sense, it's just frustrating).
Note that my above code snippet is just a small sub-sample of a much larger piece of code, so I've tried to limit to what I am actually after.