If no specifications for the structure of the target file have been made, the following rules apply:
- Each result row produces an output record.
- The sequence of the output records is determined by the result table.
- The sequence of columns in the output record is determined by the result table.
- The first column starts at position 1 of the output file.
- All output values are output in plaintext.
- The column values are separated from each other by a character string (may be empty) currently set as separator (see chapter User-specific SET Parameters).
Diverging from these default rules, it is possible to specify exactly which columns are to be written to which positions of the output record.
Example 1:
DATAEXTRACT title, name, account FROM customer;
title 01-05
name 10-19
account 20-29
OUTFILE custextract
Example 2:
DATAEXTRACT customer.cno, name, reservation.rno, hno
FROM customer, reservation
WHERE customer.cno = reservation.cno;
1 01-05
2 07-13
3 15-19
4 21-25
OUTFILE custextract
A column description assigns the values of a result column to a specific output field in the target file.
If column descriptions are used, the query should only select columns that are output or used for sorting in an ORDER BY condition.
The sequence of column descriptions is arbitrary.
The columns can be specified with their names or their sequence numbers in the result table.
The output fields are described by their starting and ending positions, just like the input fields for DATALOAD. Gaps between fields are of no consequence because LOAD fills these gaps with blanks, if necessary.
If an output field is longer than required, numerical values are justified to the right and character strings to the left. The remaining gaps are padded with blanks.
If an output field is shorter than required, character strings are truncated on the right. The execution of the DATAEXTRACT statement will be rejected for numerical values, if the loss of significant digits is to be expected.