| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The SORT command arranges the order of values in the current status list of a dimension or a dimension surrogate, or in a valueset.
Syntax
SORT dimension [byhierarchy] [bycriterion...]
where:
byhierarchy is an optional phrase that uses a parent relation to arrange the order of values in the current status list of a hierarchical dimension or its dimension surrogate, or to assign values to a valueset, based on family relationships within the hierarchy. You can include only one byhierarchy phrase in a SORT statement. It must be the first phrase in a SORT statement.
HIERARCHY parent-relation [INVERT] [DEPTH n] [SORTORPHANS]
bycriterion uses an explicit criterion to arrange the order of values in the current status list of a dimension or its dimension surrogate, or to assign values to a valueset. You can include as many bycriterion phrases as you want in a SORT statement.
{A|D} [NAFIRST] criterion
Arguments
A text expression whose value is the name of a dimension, a dimension surrogate, or a valueset.
Specifies that Oracle OLAP is to sort dimension values based on the values position in parentrel.
Specifies the name of a child-parent self-relation for dimension. For each dimension value, the relation holds another value of the dimension which is its parent dimension value (the one immediately above it in a given hierarchy). This parent relation can have more than one dimension.
Tip:
You can specify a QDR of parent-relation to specify a single value.Indicates that Oracle OLAP places the children in the hierarchy before their parents. (By default, children are placed after their parents.)
Specifies the number of generations down from the top of the hierarchy that Oracle OLAP should place into status and. there, for the values that are included in the result. The default value of n is 99. When you do not want any values in the result (that is, when you want a NULL status), specify -1 for n. When you only want the top of the hierarchy in status (that is, those dimension values that do not have parents), specify 0 (zero).
Specifies that all first cousins whose parents are not in status are sorted together. By default, Oracle OLAP preserves the hierarchical structure when sorting children even when their parents are not in status.
The order in which the values are to be sorted. A means ascending order (alphabetical when the sorting criterion is TEXT, ID, or a relation), and D means descending order (reverse alphabetical when the sorting criterion is TEXT, ID or a relation).
Specifies that NA values are to placed first in the sort rather than last.
The expression to be used as a sorting criterion. Each criterion must be dimensioned by dimension. The first expression is the major sorting criterion. When the expression is multidimensional, SORT uses the first value in status for all dimensions other than the dimension being sorted. You cannot use a valueset as the sorting criterion.
Notes
Sorting a Dimension and a Valueset
When Oracle OLAP sorts a dimension, it sorts the temporary status list of a dimension, not the data dimensioned by it. Since many OLAP DML statements operate on data according to the current status of its dimensions, sorting a dimension appears to have the effect of sorting data. A dimension and any dimension surrogates for it share the same status. Therefore, a SORT statement on a dimension or any of its surrogates sorts them all.
When Oracle OLAP sorts a valueset, it sorts the actual values within the valueset. When you execute UPDATE and COMMIT commands after sorting a valueset, the values in the valueset are stored in that sorted order.
Sorting Alphabetically
To sort a TEXT or ID dimension or its valueset in alphabetical order, use the dimension itself as the sorting criterion.
SORT district A district
Sort Order for Textual Data
The sort order for textual data in an alphabetical sort is controlled by the NLS_SORT option.
Sorting a Time Dimension
The values of dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR are stored internally as numbers. Therefore, when you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in ascending order, with the dimension itself as the sorting criterion, then the values in the status list or valueset are placed in chronological order. When you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in descending order, then the values are placed in reverse chronological order.
Sorting Using a Relation as a Criterion
When you use a relation as your sorting criterion, then the sorting is done alphabetically; that is, the dimension or valueset is sorted according to an alphabetical list of the related dimension values. To use a relation as the sorting criterion and keep the related dimension values in their original order, you must use the following expression as your sorting criterion See Example 23-40, "Sorting Using a Relation as the Criterion".
CONVERT(relation, INTEGER)
Sorting Conjoint Dimensions
You can sort a conjoint dimension or its valueset by criteria dimensioned by either the conjoint dimension itself or by one of its base dimensions.
Sorting Concat Dimensions
You can sort a concat dimension or its valueset by criteria dimensioned by either the concat dimension itself or by one of its component dimensions. See Example 23-41, "Sorting Based on a Concat" and Example 23-42, "Sorting Based on a Component".
Sorting a Worksheet
You cannot use a worksheet as a sort criterion. You must first use CONVERT to specify the data type to which values of the worksheet should be converted.
Examples
Example 23-40 Sorting Using a Relation as the Criterion
This example sorts districts according to their unit sales of tents for July 1996. They are sorted first by the region to which they belong and then in descending order of dollar sales. Notice that in the following SORT statement, a relation is used as the primary sorting criterion. This means that the districts are sorted by regions listed alphabetically.
LIMIT month TO 'Jul96' LIMIT product TO 'Tents' SORT district A Region.District D sales
Assume you issue the following REPORT statement.
REPORT DOWN district HEADING 'Region' region.district sales
The preceding statement produces the following report that reflects the work of the SORT statement.
PRODUCT: Tents
--------MONTH--------
--------JUL96--------
DISTRICT Region SALES
-------------- ---------- ----------
Dallas Central 154,914.23
Chicago Central 79,934.42
Atlanta East 140,711.00
Boston East 93,972.49
Seattle West 123,700.17
Denver West 100,413.49
In the following SORT statement, CONVERT is used to keep the regions in their original order.
SORT district A CONVERT(region.district INTEGER) D sales
Assume that you issue the following REPORT statement.
REPORT DOWN district HEADING 'Region' region.district sales
The preceding statement produces the following report that reflects the work of the last SORT statement.
PRODUCT: Tents
--------MONTH--------
--------JUL96--------
DISTRICT Region SALES
-------------- ---------- ----------
Atlanta East 140,711.00
Boston East 93,972.49
Dallas Central 154,914.23
Chicago Central 79,934.42
Seattle West 123,700.17
Denver West 100,413.49
When you want the dimension to keep the sorted order of its values permanently, use a MAINTAIN statement after you sort the dimension.
SORT district A district MAINTAIN district MOVE VALUES(district) FIRST
Example 23-41 Sorting Based on a Concat
The following statements sort the concat dimension reg.dist.ccdim in ascending order based on all of its values and report the result.
sort reg.dist.ccdim d reg.dist.ccdim report reg.dist.ccdim
The preceding statement produces the following results.
REG.DIST.CCDIM -------------------- <Region: West> <Region: East> <Region: Central> <District: Seattle> <District: Denver> <District: Dallas> <District: Chicago> <District: Boston> <District: Atlanta>
The following statements sort the concat dimension reg.dist.ccdim in ascending order based on all of its values and report the result.
SORT reg.dist.ccdim A reg.dist.ccdim REPORT reg.dist.ccdim
The preceding statement produces the following results.
REG.DIST.CCDIM -------------------- <District: Atlanta> <District: Boston> <District: Chicago> <District: Dallas> <District: Denver> <District: Seattle> <Region: Central> <Region: East> <Region: West>
Example 23-42 Sorting Based on a Component
The following statements sort the concat dimension reg.dist.ccdim in ascending order based on the values of one of its base dimensions and in descending order based on the values of its other base dimension, and report the result.
SORT reg.dist.ccdim A region D district REPORT reg.dist.ccdim
The preceding statement produces the following results.
REG.DIST.CCDIM -------------------- <REGION: CENTRAL> <REGION: EAST> <REGION: WEST> <DISTRICT: SEATTLE> <DISTRICT: DENVER> <DISTRICT: DALLAS> <DISTRICT: CHICAGO> <DISTRICT: BOSTON> <DISTRICT: ATLANTA>
Example 23-43 Sorting by Hierarchy
Assume that your analytic workspace has two dimensions (geog and time), one relation (geogparent) and one variable (sales) with the following definitions.
DEFINE GEOG DIMENSION TEXT DEFINE TIME DIMENSION TEXT DEFINE GEOGPARENT RELATION GEOG <GEOG> DEFINE SALES VARIABLE INTEGER <TIME GEOG>
If you issue a REPORT statement for sales when all of the values of geog are in status, Oracle OLAP produces the following report. This report displays the values for geog in the order in which they were added to the analytic workspace.
REPORT sales
--------SALES--------
--------TIME---------
GEOG 2004 2005
-------------- ---------- ----------
USA 1,300 NA
Massachusetts 3,881 NA
Florida 3,479 NA
Boston 2,644 NA
Orlando 4,398 NA
Miami 3,294 NA
Pembroke 4,268 NA
California 1,899 NA
Texas 2,115 NA
Los Angeles 2,394 NA
San Francisco 1,334 NA
Dallas 839 NA
Houston 997 NA
However, assume that you issue a SORT statement to sort the values of geog by (1) geogparent and (2) descending by sales. After this sort if you issue a REPORT statement for sales, Oracle OLAP produces the following report. This report displays the values for geog with the states in descending order by sales, but with the cities of each state under the appropriate state.
SORT geog HIERARCHY geogparent D sales
REPORT SALES
--------SALES--------
--------TIME---------
GEOG 2004 2005
-------------- ---------- ----------
USA 1,300 NA
Massachusetts 3,881 NA
Pembroke 4,268 NA
Boston 2,644 NA
Florida 3,479 NA
Orlando 4,398 NA
Miami 3,294 NA
Texas 2,115 NA
Houston 997 NA
Dallas 839 NA
California 1,899 NA
Los Angeles 2,394 NA
San Francisco 1,334 NA
Example 23-44 Sorting Orphans of a Hierarchy
Assume that you have the same objects described in Example 23-43, "Sorting by Hierarchy". Assume also that the states of Florida and Massachusetts are not in status.
When you include the SORTORPHANS keyword in your SORT statement, the cities in Massachusetts and Florida are sorted together.
SORT geog HIERARCHY geogparent SORTORPHANS D sales
REPORT sales
------------------SALES------------------
------------------TIME-------------------
GEOG 2004 2005
-------------- -------------------- --------------------
USA 1,300 NA
Orlando 4,398 NA
Pembroke 4,268 NA
Miami 3,294 NA
Boston 2,644 NA
Texas 2,115 NA
Houston 997 NA
Dallas 839 NA
California 1,899 NA
Los Angeles 2,394 NA
San Francisco 1,334 NA
However, if you exclude the SORTORPHANS keyword, Massachusetts cities and Florida cities are sorted separately.
LIMIT geog COMPLEMENT 'Florida' 'Massachusetts'
SORT geog HIERARCHY geogparent D sales
REPORT SALES
--------SALES--------
--------TIME---------
GEOG 2004 2005
-------------- ---------- ----------
USA 1,300 NA
Pembroke 4,268 NA
Boston 2,644 NA
Orlando 4,398 NA
Miami 3,294 NA
Texas 2,115 NA
Houston 997 NA
Dallas 839 NA
California 1,899 NA
Los Angeles 2,394 NA
San Francisco 1,334 NA
