INDICATORMODE - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose  

Specifies the mode of information returned from Teradata Database in response to SQL SELECT statements.

Syntax  

where:

 

Syntax Element

Specification

INDICDATA

Alias for INDICATORMODE

OFF

Field Mode

ON

Indicator Mode

Note: The BTEQ INDICATORMODE command does not affect the data returned from Teradata Database. It only specifies the format of the data.

Usage Notes  

By default, BTEQ returns data in Field Mode, and formats it according to accompanying format settings. In Field Mode, all data values are returned in character format. When the INDICATORMODE command option is set to ON, data is returned in Indicator Mode. In Indicator Mode, BTEQ does not format data. Instead, each selected row is presented in the format specified in the Teradata SQL SELECT statement.

Each row of data returned by Teradata Database in Indicator Mode begins with the indicator variables for the data values in that row. One indicator bit corresponds to each data item, indicating whether or not a value represents a null as follows:

  • 0 – Indicates that the value contained in a data item is not null.
  • 1 – If the field is nullable, indicates that the data item contains a null value.
  • Note: Teradata Database returns an error message if imported data has a null value in a field that is not nullable.

    The DataInfo parcel, which immediately precedes the first response row returned by Teradata Database, contains information on the total number of columns returned by a request, and the data type and length of each column.

    Specifying .EXPORT INDICDATA automatically sets Indicator Mode. Specifying .EXPORT REPORT, EXPORT RESET, .SET RECORDMODE OFF, or .SET INDICATORMODE OFF automatically resets the default Field Mode.

    If the INDICATORMODE command has not been used, the value is OFF by default. If the command is used and ON or OFF is not specified, BTEQ sets INDICATORMODE to ON.

    The INDICATORMODE command is valid in a Teradata SQL macro.

    Example  

    To return data to a client system in native format, enter the following commands:

    .database workforce;
    .set defaults
    .set indicatormode on
    select empno
    ,proj_id from charges
    where empno = 10004
    order by proj_id
    ;
    .indicatormode off
    = 1
    .defaults
    .format off
    .logoff
    .exit

    BTEQ Response

     

    *** New default database accepted .
    *** Success, Stmt# 1 ActivityCount = 2
    *** Query completed. 2 rows found. 2 columns
    returned.
    *** Dump of Parcel DATAINFO: 0000 0002 01F4 0002
    01C4 0008 * ...4...D..*
    *** Record#1 . Dump Of Data: 0000 0027 14C5 D5C7
    60F0 F0F0 F2 * ...ENG-0002*
    *** Record#2 . Dump Of Data: 0000 0027 14C5 D5C7
    60F0 F0F0 F3 * ...ENG-0003*
    *** Query completed. 2 rows found. 2 columns
    returned.
    Employee Project
    Id Id
    -------- --------
    10004 ENG-0002
    10004 ENG-0003

    Example 2

    The Example 1 command in a Teradata SQL macro appears as:

    ECHO '.SET INDICATORMODE ON';

    Example 3

    To return data in Field Mode after the INDICATORMODE command option has been set to ON, type:

    .SET INDICATORMODE OFF