LIMITSTRICT

The LIMITSTRICT option is a BOOLEAN option that determines how Oracle OLAP behaves when a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value.

Syntax

LIMITSTRICT = YES | NO

Parameters

YES: (Default) When a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value, Oracle OLAP stops executing the limit and issues an error.
NO: When a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value, Oracle OLAP processes the limit while treating the specified value as an NA.

Examples

Example 5-44 Limiting with LIMITSTRICT Set to YES

Assume that you have two dimensions (prod and year) and one variable (sales) with the following definitions and values.

DEFINE prod DIMENSION TEXT
DEFINE year DIMENSION TEXT
DEFINE sales VARIABLE INTEGER <prod year>
 
PROD
--------------
Radios
TVs
 
YEAR
--------------
2003
2004
 
               --------SALES--------
               --------PROD---------
YEAR             Radios      TVs
-------------- ---------- ----------
2003                2,459      3,534
2004                3,366      3,018

When LIMITSTRICT is set to YES, then Oracle OLAP treats requests to limit by the nonexistent prod value of 'IDontExist', as a request to limit by an invalid value:

Limiting prod to just nonexistent value, results in the error message ORA-34706 and does not change the values in status for prod.

 
->LIMIT prod to 'Idontexist'
ORA-34706: Idontexist is not a valid TESTLIMITSTRICT!PROD.
 
->REPORT prod
 
PROD
--------------
Radios
TVs

Limiting prod to a list of values that includes the nonexistent value results in the error message ORA-34706 and does not change the values in status for prod
```
->LIMIT prod to 'Idontexist' 'Radios'
ORA-34706: Idontexist is not a valid TESTLIMITSTRICT!PROD.
 
->REPORT prod

PROD
--------------
Radios
TVs
```

Specifying a nonexistent prod value in a QDR for sales also results in the error message ORA-34706.

->REPORT sales (year '2004'prod 'IDontExist')
ORA-34706: IDontExist is not a valid TESTLIMITSTRICT!PROD.

Example 5-45 Limiting with LIMITSTRICT Set to NO

Assume that you have the same two dimensions (prod and year) and variable (sales) described in Example 5-44, "Limiting with LIMITSTRICT Set to YES".

When LIMITSTRICT is set to NO, then Oracle OLAP treats requests to limit by the nonexistent prod value of 'IDontExist', as a request to limit by an NA value:

Limiting prod to just nonexistent value, results in the error message ORA-35654 and does not change the values in status for prod.

->LIMIT prod to 'Idontexist'
ORA-35654: The status of the TESTLIMITSTRICT!PROD dimension cannot be set to null.

->REPORT prod
PROD
--------------
Radios
TVs

Limiting prod to a list of values that includes a nonexistent value does not result in an error message. Instead, prod is limited to the existing values.
```
->LIMIT prod to 'Idontexist' 'Radios'
 
->REPORT prod

PROD
--------------
Radios
```
Specifying a nonexistent prod value in a QDR for sales does not result in an error message. Instead, a report of sales displays an NA value.
```
->REPORT sales (year '2004'prod 'IDontExist')
----------        NA
```