You can, if you wish, associate an indicator host variable with any main host variable. The value of indicator variables is set by the sending agent (client application-to-Teradata Database or Teradata Database-to-client application) in a client-server data exchange to inform the receiving agent if the host variable is null.
Syntax
[:] host_variable_name [INDICATOR] :indicator_variable_name
| Syntax element … | Specifies … |
|---|---|
| host_variable_name | The name of a host variable with which indicator_variable_name is associated. |
| INDICATOR | An optional keyword to help distinguish indicator_variable_name from host_variable_name. |
| indicator_variable_name | The name of the indicator variable |
Rules and Guidelines for Indicator Variables
- All indicator variables must be preceded by a COLON character, whether a COLON character precedes its associated main host variable or not.
- Specify the indicator host variable immediately following the main host variable with which it is associated (for example, :MainVar:IndVar or :HostMainVar:HostIndVar).
To avoid confusion, precede the indicator variable specification by the word INDICATOR (that is, :MainVar INDICATOR :IndVar).
- Indicator variables can be used in WHERE clause conditions.
How Indicator Variables Are Used With Host Variables
For an input host variable, the application program uses an indicator variable to inform the Teradata Database if the host variable is null.
For an output host variable, the Teradata Database uses an indicator variable to inform the application program if the host variable is null or was truncated when the value was placed into the host variable.
The following table defines the relationship between indicator variable values and input host variables.
| This indicator variable value … | Reports that its associated input host variable is … |
|---|---|
| -n
where n is typically 1 |
null. |
| 0 | non-null and successfully returned to the output host variable. |
| +n | truncated. The truncation occurred when returning a character or byte string to the main variable associated with the indicator variable. The value n reports the original length of the string before truncation. |
Processing of Indicator Variables
- One indicator variable corresponds to each data item (field) of a response row.
- Each indicator variable occupies one bit of space.
- If there are n data fields, the first (n + 7)/8 bytes of a response row contain the indicator variables for the data in that row.
For example, if a response row contains 19 data items, then (19 + 7)/8 = 3 bytes contain the indicator variables for that row.
- Indicator variables are held in the minimum number of 8-bit bytes required to store them.
Unused bits are set to binary 0.
Internal Processing of Indicator Variables
Internally, the Teradata Database uses CLIv2 Indicator mode to return data in the NullIndicators field of the Data field of a Record parcel in the internal format used by the client system.
Immediately preceding the first response row is a DataInfo parcel containing information on the total number of columns returned, the data type, and length of each column.
Each response row begins with indicator variables corresponding to each data item in that row.
Indicator Variables and DateTime and Interval Data
DateTime and Interval values are cast as CharFix, and the DataInfo parcel created for Indicator Variable output follows that rule with the exception of DATE values in INTEGERDATE (Teradata Database-style DATE) mode.
You can export values in IndicData mode and subsequently import in Data mode with a USING phrase built to properly type any DateTime or Interval values in the import records.
If the exported values are to be used as data for INSERT or UPDATE statements, the Teradata Database implicitly casts USING values that are CharFix and have the right length for the target DateTime or Interval type.
See “USING request modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Example: The Indicator Variable
In this example, when the value for the indicator variable is -1, the employee number or department number is set to null.
When the indicator variable is 0, then the employee number or department number is set to the value reported to the host variable.
Example: Defining the Department Number As Null
In this example, Department Number is defined to be null.
MOVE -1 TO DEPTNO-INDIC. EXEC SQL UPDATE EMPLOYEE SET DEPARTMENT_NUMBER = :DEPTNO INDICATOR :DEPTNO-INDIC END-EXEC.EXEC SQL INSERT INTO EMPLOYEE VALUES (:EMPNO INDICATOR :EMPNO-INDIC,:DEPTNO INDICATOR :DEPTNO-INDIC) END-EXEC.