0

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.

  • I've managed a work around for this, but am not considering this issue resolved as it is only covering up the underlying issue, not resolving it. I've placed the entire above query inside a CTE, then referenced the CTE with a WHERE filter as I mentioned above. This removes the additional iterated rows, but does not stop the underlying query processor from iterating across those rows. E.g. the additional processing occurs, and therefore is using up system resources that do not need to be used. – Nexmilitis Jul 22 '19 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.