Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
  - Differences between SETVARIABLE, SETMAXVARIABLE, and SETMINVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID_STRING
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

Multidimensional array

A multidimensional array is an array of arrays. You can use a subscript operator to access a primitive element in an array at the innermost level. You can also use a subscript operator to access an array at any level.

You can use subscript operators to return the following values:

A primitive element in an array at the innermost level.

One or more arrays at any level.

A subset of one or more arrays at any level.

To access a primitive element in an array at the innermost level, you use more than one subscript operator. The number of dimensions in a multidimensional array determines the number of subscript operators to use. Each subscript operator must contain one index value. The data type of the return value is the same as the data type of the primitive elements in the array.

For example, in a two-dimensional array, you use two subscript operators. The first subscript operator accesses the parent array. The second subscript operator accesses the child array within the parent array.

Examples

Consider the following two-dimensional parent array that contains three child arrays and each child array contains string elements:

menu_array = [
 ['milk','coffee','tea','chai'],
 ['ham','turkey',NULL],
 ['caesar','cobb','greek','chipotle']
]

You can use subscript operators to access the following types of elements:

Primitive elements: The following expressions use two subscript operators to access a specific string element from each child array in the parent array
menu_array
:

Input Value

RETURN VALUE

menu_array[0][1]

'coffee'

menu_array[2][3]

'chipotle'

menu_array[1][2]

NULL
Array elements: The following expressions use a single subscript operator to access the child arrays in the parent array
menu_array
:

Input Value

RETURN VALUE

menu_array[0]

['milk','coffee','tea','chai']

menu_array[0,2]

[ ['milk','coffee','tea','chai'], ['ham','turkey',NULL] ]

menu_array[1,0]

[ ]

menu_array[NULL,2]

NULL
Subset of array elements: The following expressions use two subscript operators to access a subset of child arrays in the parent array
menu_array
:

Input Value

RETURN VALUE

menu_array[0][0,2]

['milk','coffee']

menu_array[2][0,3]

['caesar','cobb','greek']

menu_array[0,2][0,3]

[ ['milk','coffee','tea'], ['ham','turkey',NULL] ]

Input Value	RETURN VALUE
menu_array[0][1]	'coffee'
menu_array[2][3]	'chipotle'
menu_array[1][2]	NULL

Input Value	RETURN VALUE
menu_array[0]	['milk','coffee','tea','chai']
menu_array[0,2]	[ ['milk','coffee','tea','chai'], ['ham','turkey',NULL] ]
menu_array[1,0]	[ ]
menu_array[NULL,2]	NULL