RECORDMODE - 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  

Returns data from SQL SELECT statements in client-oriented data representations rather than character format.

Syntax  

where:

 

Syntax Element

Specification

OFF

Field Mode (system default).

ON

Record Mode (command default).

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

Usage Notes  

By default, BTEQ returns data in Field Mode and formats it according to accompanying FORMAT command specifications. In Field Mode, BTEQ returns all data values in character format.

When returning data in Record Mode, BTEQ does not format it. Instead, BTEQ presents each row in the format specified in the Teradata SQL SELECT statement using the representation that is appropriate for the client system. BTEQ usually presents each selected row as a hexadecimal dump, ignoring FORMAT and TITLE command specifications. Null values, data types and data lengths are implicit, and Teradata SQL data conversion can be used to change the format of the data.

Record Mode is automatically set when .EXPORT DATA is specified. The default setting of Field Mode is automatically reset when specifying .EXPORT REPORT or .EXPORT RESET, or when setting either the INDICATORMODE or RECORDMODE command option to OFF.

If the RECORDMODE command is not specified, the system value is OFF by default. If the command is used and ON or OFF is not specified, BTEQ sets RECORDMODE to ON. Use the .SHOW CONTROLS command to determine what RECORDMODE is set to.

The RECORDMODE command can be used in a Teradata SQL macro.

End-of-Line Character Differences

The character string that indicates the end of a line in a BTEQ report differs between UNIX and Windows systems.

 

Operating System

End of Line Indicator

UNIX OS

\n

Windows

\r\n

This conforms to the standard line ending format for each environment. Applications need to be altered to account for this difference before running them on both types of environments.

Numeric Overflow in Reports

When in Field Mode, a numeric overflow error returned for numeric or decimal data types in a report, is reported as '***' instead of 'Error.... etc.'.

For example, the following query returns the correct format because the format accommodates the number of digits returned:

   SELECT cast (123456 as integer format '$999,999');

The result is:

     123456
   $123,456

But the next query returns an overflow reported as stars '***...':

   SELECT CAST (123456 as integer format '$99,999');

The result is:

     123456
    *******

If the same query is executed in Record or Indicator Mode, the numeric overflow is reported as an error, the preferred response.

Example  

The following example shows the results of using the RECORDMODE command to return data in client-oriented (Record Mode) representations:

   database workforce;
   .set defaults
   .set recordmode on
   select   deptno
   ,deptname     from     department
   order by deptno
   ;
   .defaults
   .set recordmode off
   .logoff
   .exit

BTEQ Response

   *** Success, Stmt# 1 ActivityCount = 5
   *** Query completed. 5 rows found. 2 columns returned.
   *** Record#1. Dump Of Data:
   0000  0064 000E C184 9489  9589 A2A3 9981 A389 
   *....Administrati*    0010  9695
            *on*
   *** Record#2. Dump Of Data:    0000  012C 000B C5A7 8583
   40D6 8686 8983 85   *....Exec Office*
   *** Record#3. Dump Of Data:    0000  01F4 000B C595 8789
   9585 8599 8995 87   *.4..Engineering*
   *** Record#4. Dump Of Data:    0000  0258 000D D481 95A4
   8681 83A3 A499 8995 *....Manufacturin*    0010  87
                                  *g*
   *** Record#5. Dump Of Data:    0000  02BC 0009 D481 9992
   85A3 8995 87        *....Marketing*

Example 2

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

   ECHO '.SET RECORDMODE ON';

To return data in Field Mode after setting the RECORDMODE command option to ON, specify another RECORDMODE command as follows:

   .SET RECORDMODE OFF