Layout definitions

A layout is an ordered collection of zone (field) definitions. You must specify a layout for the fixed-length format. Each zone definition is made up of mutually exclusive (non-overlapping) clauses.

These clauses must be in the following order, although some are optional and can be empty:
Use type
Indicates whether a zone is a normal (data) zone or a filler zone. For data zones, this value is omitted. You can specify filler zones only in bytes. Other use-types exist but are not used for fixed-length format data.
Name
The name of the zone. Duplicate zone names are not allowed. This definition is not currently used but is typically provided to identify the field.
Type
Defines the zone type. If you do not specify it, the type defaults to the corresponding type of a table column. Filler zones have no default type. Valid values are as follows:
  • CHAR
  • VARCHAR
  • NCHAR
  • NVARCHAR
  • INT1
  • INT2
  • INT4
  • INT8
  • INT
  • UINT1
  • UINT2
  • UINT4
  • UINT8
  • UINT
  • FLOATING
  • DOUBLE
  • NUMERIC
  • BOOL
  • DATE
  • TIME
  • TIMESTAMP
  • TIMETZ
Style
Defines the zone representation. This clause is optional. The default representation is based on the zone type and Format option. All other styles are valid only for their corresponding non-textual zone types. Valid values are as follows:
INTERNAL
Valid only for textual zones (char, varchar, nchar, and nvarchar).
DECIMAL
Valid for integer and numeric zone types.
DECIMALDELIM
Valid for numeric, float, double, and time-style (time, timetz, and timestamp) zone types.
FLOATING
Valid for float or double zone types.
SCIENTIFIC
Valid for float or double zone types.
YMD <'date-delim'>
Valid for date zones, including other date styles that are supported for the DateStyle and DateDelim external table options.
12Hour <'time-delim'>
Valid for time zones, including other time styles that are supported for the TimeStyle and TimeDelim external table options.
24Hour <'time-delim'>
Valid for time zones, including other time styles that are supported for the TimeStyle and TimeDelim external table options.
YMD <'date-delim'> 24Hour <'time-delim'>
Valid for timestamp and timetz zones, including other combinations of date and time styles that are supported for the DateStyle, DateDelim, TimeStyle, and TimeDelim external table options.
TRUE_FALSE, Y_N, 1_0
Valid for boolean zones, including other boolean styles that are supported for the BoolStyle external table option. The style must be in accordance with the format.
Length
Number of bytes.
Nullif
Definition of the zone nullness attribute. For fixed-format files, this clause specifies a known data pattern within the field that when present signifies that the field is null. The length is equal to or less than the column width, and the maximum length is 39 bytes.
Nulls are detailed in the following examples:
Table 1. Layout example
Use type Name Type Style Length Nullif
NA f1 int4 DECIMAL Bytes 10 Nullif @ = 0
NA f2 date YMD Bytes 10 Nullif &= '2000-10-10'
NA f3 char(20) INTERNAL Chars 10 Nullif && ''
Filler f4 char(10) NA Bytes 10 NA