Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Part Number B28126-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CATEGORIZE

The CATEGORIZE function groups the values of a numeric expression into categories. You define the categories by specifying a series of increasing numeric values. The result that CATEGORIZE returns is dimensioned by all the dimensions of expression. For each cell in expression, CATEGORIZE returns one of the following: the category in which the number falls, zero (0) for a value below the range of the first category, minus one (-1) for a value above the range of the last category, or NA for an NA value.

Return Value

DECIMAL

Syntax

CATEGORIZE(expression {values|group-expression})

where values has the following syntax:

     bottom-value [next-lowest-break-valuetop-value

Arguments

expression

The numeric expression whose values are to be categorized.

bottom-value

A number that specifies the lowest number in the series and sets the bottom limit of category 1.

next-lowest-break-value

A number that specifies the beginning of the range of the next category.

top-value

A number that specifies the highest number in the series and sets the upper limit of the highest category.

group-expression

A one-dimensional numeric expression that defines the break values for the categories.

Examples

Example 7-36 Specifying Category Range Values

Assume that your analytic workspace contains the following geography and items dimensions and sales2 variable.

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

Assume the sales2 variable has the following data values.

-------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00

This statement reports the result of categorizing the sales2 variable.

REPORT CATEGORIZE(sales2 10 15 20 25)

The preceding statement produces the following output.

-CATEGORIZE(SALES2 10 15 20 25)-
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               -1.00       2.00       1.00
Item2                1.00       3.00       2.00
Item3                2.00       3.00       3.00
Item4               -1.00       3.00       3.00
Item5                  NA       0.00       3.00

Example 7-37 Specifying a Group-Expression

These statements define a groups dimension and a groupval variable.

DEFINE groups DIMENSION TEXT
MAINTAIN groups ADD 'Grp1' 'Grp2' 'Grp3' 'Grp4'
DEFINE groupvals DECIMAL <groups>
groupvals(groups 'Grp1') = 10
groupvals(groups 'Grp2') = 15
groupvals(groups 'Grp3') = 20
groupvals(groups 'Grp4') = 25

This statement reports the result of calling the CATEGORIZE function with the sales variable as the expression argument and the groupvals variable as the group-expression argument of the call.

REPORT CATEGORIZE(sales, groupvals)

The preceding statement produces the same output as the statement in the "Specifying Category Range Values" .