17.10 - Setting the Value of Array Elements - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

Teradata provides the following library functions for setting the element values of an ARRAY result parameter.

Library Function Description
FNC_SetArrayElements Sets one or more elements of an ARRAY to the same new value.
FNC_SetArrayElementsWithMultiValues Sets the value of one or more elements of an ARRAY where each element can be set to a different value.

The following procedure shows the typical steps you can use to set a range of ARRAY elements to the same new value.

  1. Set the nullIndicatorargument to 0.
  2. Set the newValue argument to the value you want to assign to the range of elements.
  3. Call FNC_GetArrayTypeInfo to find out the number of dimensions in the ARRAY.
  4. Allocate the structure that provides the index to the set of ARRAY elements that you want to modify. Set the values of the structure to the range of elements.
  5. Call FNC_SetArrayElements to set the value of the specified range of elements to newValue.
  6. Call FNC_free to release allocated resources once you have processed the data.

For details, see FNC_SetArrayElements.

The following procedure shows the typical steps you can use to set a range of ARRAY elements to different values.

  1. Call FNC_GetArrayTypeInfo to find out the number of elements in the ARRAY. Alternatively, you can call FNC_GetArrayElementCount to find out the total number of elements that are currently present in the ARRAY argument.
  2. Allocate and initialize a new NullBitVector array. For details, see Checking and Setting the NullBitVector.
  3. Allocate the structure that provides the index to the set of ARRAY elements that you want to modify. Set the values of the structure to the range of elements.
  4. Allocate the newValues buffer to be the size corresponding to the number of elements to be modified. Fill the buffer with the desired element value for each element in the specified range, in row-major order.
  5. Use FNC_SetNullBitVector or FNC_SetNullBitVectorByElemIndex to set the bits of the NullBitVector to indicate the elements that will be set to a value or to NULL.
  6. Call FNC_SetArrayElementsWithMultiValues to set the specified range of elements to the new values based on the values of the newValues buffer and the NullBitVector.
  7. Call FNC_free to release allocated resources once you have processed the data.

Alternatively, you can call FNC_GetArrayElements first to get multiple values from an ARRAY and a corresponding NullBitVector. Then you can optionally modify both of these structures and use FNC_SetArrayElementsWithMultiValues to update the same range of elements in a different ARRAY that has the same ARRAY data type.

For more information, see FNC_SetArrayElementsWithMultiValues.