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