Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
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

7 OLAP DML Functions: A - K

This chapter contains the following topics:

Reference topics for the remaining OLAP DML functions appear in alphabetical order in Chapter 8, "OLAP DML Functions: L - Z".

For other OLAP DML reference topics, see Chapter 4, "OLAP DML Properties", Chapter 5, "OLAP DML Options", Chapter 9, "OLAP DML Commands: A-G", and Chapter 10, "OLAP DML Commands: H-Z".

About OLAP DML Functions

OLAP functions work in much the same way as functions work in other programming languages. They initiate action and return a value. The one exception is the looping nature of OLAP DML functions as discussed in "OLAP DML Statements Apply to All of the Values of a Data Object".

Most of the OLAP DML functions are standard text and calculation functions. Other OLAP DML functions return more complex information. For example, the OLAP DML provides the AW and OBJ functions that you can use to retrieve many different types of information about an analytic workspace and its objects and the AGGREGATE function that you can use to calculate aggregate data on-the-fly at user request.

Additionally, you can augment the functionality of the OLAP DML by writing an OLAP DML program for use as a function.

Tip:

Many OLAP DML statements can be coded as a 3-character abbreviation that consists of the first letter of the statement plus the next two consonants.

Functions: Alphabetical Listing

A B C D E F G H I J K L M N O P Q R S T U V W Y

A


ABS
ADD_MONTHS
AGGCOUNT
AGGMAPINFO
AGGREGATE function
AGGREGATION
AGGROPS
ALLOCOPS
ANTILOG
ANTILOG10
ANY
ARCCOS
ARCSIN
ARCTAN
ARCTAN2
ARG
ARGCOUNT
ARGFR
ARGS
ASCII
ASCIISTR
AVERAGE
AW function

B


BACK
BASEDIM
BASEVAL
BEGINDATE
BIN_TO_NUM
BITAND
BLANKSTRIP

C


CALLTYPE
CHARTOROWID
CATEGORIZE
CEIL
CHANGEBYTES
CHANGECHARS
CHANGEDRELATIONS
CHANGEDVALUES
CHARLIST
CHGDIMS
CHR
COALESCE
COLVAL
CONTEXT function
CONVERT
CORRELATION
COS
COSH
COUNT
CUMSUM
CURRENT_DATE
CURRENT_TIMESTAMP

D


DAYOF
DBTIMEZONE
DDOF
DECODE
DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD

E


ENDDATE
ENDOF
EVERY
EXISTS
EXP
EVERSION
EXTBYTES
EXTCHARS
EXTCOLS
EXTLINES
EXTRACT

F


FCOPEN
FCQUERY
FILEERROR
FILEGET
FILENEXT
FILEOPEN
FILEQUERY
FILTERLINES
FINDBYTES
FINDCHARS
FINDLINES
FINTSCHED
FLOOR
FPMTSCHED
FROM_TZ

G


GET
GREATEST
GROUPINGID function
GROWRATE

H


HEXTORAW
HIERCHECK
HIERHEIGHT function
HIERSHAPE

I


INFO
INITCAP
INLIST
INSBYTES
INSCHARS
INSCOLS
INSLINES
INSTAT
INSTR functions
INTPART
IRR
ISDATE
ISEMPTY
ISINFINITE
ISNAN
ISSESSION
ISVALUE

J


JOINBYTES
JOINCHARS
JOINCOLS
JOINLINES

K


KEY

L


LAG
LAGABSPCT
LAGDIF
LAGPCT
LARGEST
LAST_DAY
LEAD
LEAST
LIMIT function
LIMITMAPINFO
LNNVL
LOCALTIMESTAMP
LOG function
LOG10
LOWCASE
LOWER
LPAD
LTRIM

M


MAKEDATE
MAX
MAXBYTES
MAXCHARS
MEDIAN
MIN
MMOF
MODE
MODULO
MONTHS_BETWEEN
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL

N


NA2
NAFILL
NAFLAG
NEW_TIME
NEXT_DAY
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLSSORT
NONE
NORMAL
NPV
NULLIF
NUMBYTES
NUMCHARS
NUMLINES
NUMTODSINTERVAL
NUMTOYMINTERVAL
NVL
NVL2

O


OBJ
OBJLIST
OBJORG
OBSCURE
ORA_HASH

P


PARTITION
PARTITIONCHECK
PERCENTAGE

Q


QUAL

R


RANDOM
RANK
RAWTOHEX
RECNO
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REM
REMAINDER
REMBYTES
REMCHARS
REMCOLS
REMLINES
REPLACE
REPLBYTES
REPLCHARS
REPLCOLS
REPLLINES
RESERVED
ROUND
ROW function
ROWIDTOCHAR
ROWIDTONCHAR
RPAD
RTRIM
RUNTOTAL

S


SESSIONTIMEZONE
SIGN
SIN
SINH
SMALLEST
SMOOTH
SORT function
SORTLINES
SOUNDEX
SQLFETCH
SQRT
STARTOF
STATALL
STATDEPTH
STATEQUAL
STATFIRST
STATLAST
STATLEN
STATLIST
STATMAX
STATMIN
STATRANK
STATVAL
STDDEV
SUBSTR functions
SUBTOTAL
SYS_CONTEXT
SYSDATE
SYSINFO
SYSTEM
SYSTIMESTAMP

T


TALLY
TAN
TANH
TCONVERT
TEXTFILL
TO_ BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_DATE
TO_DSINTERVAL
TO_NCHAR
TO_NUMBER
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TOD
TODAY
TOTAL
TRANSLATE
TRIGGER function
TRIM
TRUNCATE
TZ_OFFSET

U


UNIQUELINES
UNRAVEL
UPPER
UPPER

V


VALSPERPAGE
VALUES
VINTSCHED
VPMTSCHED
VSIZE

W


WEEKOF
WIDTH_BUCKET
WKSDATA
WRITABLE

Y


YYOF

Functions by Category

The OLAP DML provides the typical numeric and text functions:

Conversion functions
Datetime functions
Date-only functions
Financial functions
File management functions
NA functions
Numeric (general) functions
Numeric aggregation functions
Program argument and context functions
Reporting functions
Statistical and forecasting functions
Text functions

It also has functions that are unique to its data model:

Analytic workspace and object information functions
Aggregation, allocation, and model specification functions
System and Database information functions
Time-series functions
Object value retrieval functions
Status manipulation functions

Analytic workspace and object information functions


AW function
CHANGEDRELATIONS
CHANGEDVALUES
BASEDIM
EXISTS
ISEMPTY
ISSESSION
ISVALUE
LIMITMAPINFO
OBJ
OBJLIST
OBJORG
PARTITION
RESERVED
TALLY
TRIGGER function
VALSPERPAGE
VSIZE
WRITABLE

Aggregation, allocation, and model specification functions


AGGCOUNT
AGGMAPINFO
AGGREGATE function
AGGREGATION
AGGROPS
ALLOCOPS
HIERCHECK
INFO
CHANGEDRELATIONS
CHANGEDVALUES
ISEMPTY
PARTITIONCHECK

Conversion functions


ASCII
ASCIISTR
BIN_TO_NUM
CHR
CONVERT
FROM_TZ
HEXTORAW
RAWTOHEX
TCONVERT
TO_ BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_DATE
TO_NCHAR
TO_NUMBER
TO_TIMESTAMP
TO_TIMESTAMP_TZ

System and Database information functions


CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
LOCALTIMESTAMP
SESSIONTIMEZONE
SYS_CONTEXT
SYSDATE
SYSINFO
SYSTEM

Datetime functions


ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
ROUND (datetime)
NEW_TIME
NUMTODSINTERVAL
NUMTOYMINTERVAL
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TOD
TRIM
TRUNCATE (datetime)
TZ_OFFSET

Date-only functions


BEGINDATE
DAYOF
DDOF
ENDDATE
ENDOF
ISDATE
MAKEDATE
MMOF
STARTOF
TODAY
WEEKOF
YYOF

Financial functions


DEPRDECL
DEPRDECLSW
DEPRSL
DEPRSOYD
FINTSCHED
FPMTSCHED
GROWRATE
IRR
NPV
VINTSCHED
VPMTSCHED

File management functions


FILEERROR
FILEGET
FILENEXT
FILEOPEN
FILEQUERY
GET
RECNO

NA functions


COALESCE
NA2
NAFILL
NAFLAG
NULLIF
NVL
NVL2

Numeric (general) functions


ABS
ANTILOG
ANTILOG10
ARCCOS
ARCSIN
ARCTAN
ARCTAN2
BIN_TO_NUM
BITAND
CEIL
COS
COSH
DECODE
EXP
FLOOR
GREATEST
INTPART
ISINFINITE
ISNAN
LEAST
LOG function
LOG10
MAX
MIN
MODULO
NULLIF
ORA_HASH
RANDOM
RANK
REM
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SMOOTH
SORT function
SQRT
TAN
TANH
TRUNCATE (number)
VSIZE
WIDTH_BUCKET

Numeric aggregation functions


ANY
AVERAGE
COUNT
EVERY
LARGEST
MEDIAN
MODE
NONE
PERCENTAGE
SMALLEST
TCONVERT
TOTAL

Object value retrieval functions


BASEVAL
CHGDIMS
HIERHEIGHT function
KEY
QUAL
ROW function
SORT function
UNRAVEL
VALUES
WKSDATA

Program argument and context functions


ARG
ARGCOUNT
ARGFR
ARGS
BACK
CALLTYPE
CONTEXT function

Reporting functions


COLVAL
RUNTOTAL
SUBTOTAL

Statistical and forecasting functions


CATEGORIZE
CORRELATION
FCOPEN
FCQUERY
INFO
NORMAL
ORA_HASH
RANDOM
STDDEV

Status manipulation functions


CHGDIMS
INSTAT
LIMIT function
STATALL
STATDEPTH
STATEQUAL
STATFIRST
STATLAST
STATLEN
STATLIST
STATMAX
STATMIN
STATRANK
STATVAL
VALUES

Text functions


ASCII
ASCIISTR
BLANKSTRIP
CHANGEBYTES
CHANGECHARS
CHARLIST
EXTBYTES
EXTCHARS
EXTCOLS
EXTLINES
FILTERLINES
FINDBYTES
FINDCHARS
FINDLINES
INITCAP
INLIST
INSBYTES
INSCHARS
INSCOLS
INSLINES
INSTR functions
JOINBYTES
JOINCHARS
JOINCOLS
JOINLINES
LEAST
LENGTH functions
LOWCASE
LOWER
LPAD
LTRIM
MAXBYTES
MAXCHARS
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLSSORT
NULLIF
NUMBYTES
NUMCHARS
NUMLINES
OBSCURE
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REMBYTES
REMCHARS
REMCOLS
REMLINES
REPLACE
REPLBYTES
REPLCHARS
REPLCOLS
REPLLINES
RPAD
RTRIM
SORT function
SORTLINES
SOUNDEX
SUBSTR functions
TEXTFILL
TRANSLATE
TRIM
UNIQUELINES
UPPER
UPPER
VSIZE

Time-series functions


CUMSUM
LAG
LAGABSPCT
LAGDIF
LAGPCT
LEAD
MOVINGAVERAGE
MOVINGMAX
MOVINGMIN
MOVINGTOTAL