SORT function

The SORT function returns the dimension or dimension surrogate values that result from a specified SORT command.

Return value

The return value varies depending on the use of the function:

• When the SORT function is an argument to an OLAP DML statement (including a user-defined command or function) that expects a valueset, it returns a valueset. When the SORT function returns an empty valueset, it returns it as a valueset with null status.

• When you include the INTEGER keyword, the SORT function returns the position numbers of the values as INTEGERS.

• In all other cases, the SORT function returns either a TEXT value. When it returns a TEXT value that represents empty status, it returns the value as NA.

Syntax

SORT([INTEGER]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

See the SORT command for a complete description of all arguments except INTEGER.

INTEGER

When you use the INTEGER keyword, the function returns the position numbers of the values in the default dimension status rather than the names. When you use INTEGER with a valueset, the function returns the position numbers of the values in the default dimension status, not in the valueset.