![]() Previous |
![]() Next |
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
(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.
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
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.
Limiting with LIMITSTRICT Set to NO
Assume that you have the same two dimensions (prod
and year
) and variable (sales
) described in Example: 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