QUAL

The QUAL function lets you explicitly specify a qualified data reference (QDR). You should use QUAL in cases where the syntax of a QDR is ambiguous and could either be misinterpreted by Oracle OLAP or cause a syntax error.

QDRs provide a mechanism for limiting one or more dimensions of an expression to a single value. QDRs are useful when you want to temporarily reference a value that is not in the current status.

Return Value

The value that is returned has the same data type as the expression being qualified.

Syntax

QUAL(expression, dimname1 dimexp1 [, dimnameN dimexpN])

Arguments

expression

The expression being qualified. You should use QUAL to qualify complex expressions that contain computation, function calls, or ampersand substitution. You can also use QUAL when the expression is a simple variable name. However, QUAL is not required for simple expressions, and you can use the following standard QDR syntax.

expression(dimname1 dimexp1 [, dimname2 dimexp2 ...])

dimname

The dimension to be limited. You can specify one or more of the dimensions of the expression. Each dimension must be paired with a dimexp. You can specify a dimension surrogate instead of the dimension.

dimexp

An expression that represents the value to which the dimension should be limited. The expression can be a value of the dimension, a text expression whose result is a value of the dimension, a numeric expression whose result is the logical position of a value of the dimension, or a relation of the dimension.

When the dimension being limited is a conjoint dimension, then dimexp must be enclosed in angle brackets and must include a value for each of its base dimensions.

When the dimension being limited is a concat dimension, then dimname and dimexp can be one of the combinations listed in Table 19-1, "Valid dimname and dimexp Combinations for Concat Dimensions".

Table 19-1 Valid dimname and dimexp Combinations for Concat Dimensions

dimname	dimexp
The name of the concat dimension	A value of the concat dimension
The name of the concat dimension	The name of a base dimension
The name of a base dimension of the concat dimension	A value of the base dimension
The name of a base dimension of the concat dimension	The name of the concat dimension

Examples

Example 19-48 Using QUAL with MAX

The following example first shows how you might view your data by limiting its dimensions, and then how you might view it by using QUAL.

Assume that you issue the following OLAP DML statements to limit the view of the Cogs line data in the Sporting division to January 1996 through June 1996, and, then, report by month on the maximum value of actual costs or budgeted costs or MAX(actual,budget), actual costs, and budgeted costs for each month.

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT line TO 'Cogs'
LIMIT division TO 'Sporting'
REPORT DOWN month W 11 MAX(actual,budget) W 11 actual W 11 budget

The preceding statements produce the following report.

DIVISION: SPORTING
               ---------------LINE----------------
               ---------------COGS----------------
               MAX(ACTUAL,
MONTH            BUDGET)     ACTUAL      BUDGET
-------------- ----------- ----------- -----------
Jan96           287,557.87  287,557.87  279,773.01
Feb96           323,981.56  315,298.82  323,981.56
Mar96           326,184.87  326,184.87  302,177.88
Apr96           394,544.27  394,544.27  386,100.82
May96           449,862.25  449,862.25  433,997.89
Jun96           457,347.55  457,347.55  448,042.45

Now consider how you might view the same figures for MAX(actual,budget) without changing the status of line or division.

ALLSTAT
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
   W 11 HEADING 'MAX(actual,budget)'-
   QUAL(MAX(actual,budget), line 'Cogs', division 'Sporting')

For Cogs in
Sporting       MAX(actual,
Division         budget)
-------------- -----------
Jan96           287,557.87
Feb96           323,981.56
Mar96           326,184.87
Apr96           394,544.27
May96           449,862.25
Jun96           457,347.55

When you attempt to produce the same report with standard QDR syntax, Oracle OLAP signals an error.

REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
   W 11 HEADING 'MAX(actual,budget)'-
   MAX(actual,budget) (line cogs, division sporting)

The following error message is produced.

ERROR: A right parenthesis or an operator is expected after LINE.

Example 19-49 Using QUAL with a Concat Dimension

The following example shows two ways of limiting the values of a concat dimension in a QUAL function. The reg.dist.ccdim concat dimension has region and district as its base dimensions. The rdsales variable is dimensioned by month, product, and reg.dist.ccdim.

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT product TO 'Tents' 'Canoes'
 
" Limit the concat by specifying one of its component dimensions
REPORT W 30 QUAL(rdsales * 2, month 'Feb96', district 'Boston')

These statements produce the following report.

QUAL(RDSALES * 2, MONTH
PRODUCT          'Feb96', DISTRICT 'Boston')
-------------- ------------------------------
Tents                               69,283.18
Canoes                             164,475.36
 
" Limit the concat by specifying one of its values
REPORT W 30 QUAL(rdsales * 2, month 'Mar96', reg.dist.ccdim  '<district: Boston>')
 
                  QUAL(RDSALES * 2, MONTH
                  'Mar96', REG.DIST.CCDIM
PRODUCT            '<district: Boston>')
-------------- ------------------------------
TENTS                               91,484.42
CANOES                             195,244.56