Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.6
- 10.5.9
- 10.5.8
- 10.5.7
- 10.5.4
- 10.5.1
- 10.5
- 10.4.0
- 10.2.2 Service Pack 1
- 10.2.2
- 10.2.1

Back Next

CUME

Returns a running total. A running total means CUME returns a total each time it adds a value. You can add a condition to filter rows out of the row set before calculating the running total.

Use CUME and similar functions (such as MOVINGAVG and MOVINGSUM) to simplify reporting by calculating running values.

Syntax

CUME( numeric_value [, filter_condition] )

The following table describes the arguments for this command:

Argument	Required/ Optional	Description
numeric_value	Required	Numeric datatype. Passes the values for which you want to calculate a running total. You can enter any valid transformation expression. You can create a nested expression to calculate a running total based on the results of the function as long as the result is a numeric value.
filter_condition	Optional	Limits the rows in the search. The filter condition must be a numeric value or evaluate to TRUE, FALSE, or NULL. You can enter any valid transformation expression.

Return Value

Numeric value.

NULL if all values passed to the function are NULL, or if no rows are selected (for example, the filter condition evaluates to FALSE or NULL for all rows).

If the return value is Decimal with precision greater than 15, you can enable high precision to ensure decimal precision up to 38 digits.

Nulls

If a value is NULL, CUME returns the running total for the previous row. However, if all values in the selected port are NULL, CUME returns NULL.

Examples

The following sample rowset might result from using the CUME function:

CUME( PERSONAL_SALES )

PERSONAL_SALES	RETURN VALUE
40000	40000
80000	120000
40000	160000
60000	220000
NULL	220000
50000	270000

Likewise, you can add values before calculating a running total:

CUME( CA_SALES + OR_SALES )

CA_SALES	OR_SALES	RETURN VALUE
40000	10000	50000
80000	50000	180000
40000	2000	222000
60000	NULL	222000
NULL	NULL	222000
50000	3000	275000

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

CUME

CUME

Syntax

Return Value

Nulls

Examples