DEFINE FIELD command

Used to define physical data fields in ACL table layouts.

Syntax

DEFINE FIELD field_name data_type start_position length <decimals|date_format>
<NDATETIME> <PIC format> <AS alternate_column_title> <WIDTH value> <field_note>

Parameters

field_name

Specifies the name of the field. The name can contain up to 31 upper and lowercase alphanumeric characters, including the underscore character (_). To create a two-word name, insert an underscore between the words.

data_type

Specifies how you want ACL to interpret the data. For example, a field of transaction numbers might be stored as numeric values in the data source. If you do not want to perform calculations on these numbers because they represent invoice numbers, you can define the field as character data instead.

For a list of valid ACL data types, see the ACL Analytics User Guide.

start_position

Specifies the byte position the field starts at.

length

Specifies the length of the field in bytes.

decimals

Optional. Applies only to numeric fields. Specifies the number of decimal places.

date_format

Optional. Applies only to date fields. For datetime or time fields, use the PIC parameter instead. You can also use the PIC parameter for date fields.

Specifies the date format in the source data. If the source data includes separators such as slashes, you must include the separators in the date_format parameter. For example, if the source data is 12/31/2014, you must enter the format as MM/DD/YYYY. Do not enclose the date_format parameter in quotation marks.

NDATETIME

Optional. Specifies that date, datetime, or time values stored in a numeric field are treated as datetime data. The NDATETIME parameter requires that you also specify the source datetime format using the PIC parameter.

PIC format

Optional. Applies only to numeric or datetime fields. Specifies the display format of numeric fields in views and reports, or the input format of datetime fields in source data (separators, decimals, order of date and time characters, etc.). The format parameter must be enclosed in quotation marks.

AS alternate_column_title

Optional. Specifies a column title that is different from the field name.

WIDTH value

Optional. Specifies the display width for the field. This value is used as the column size when displaying the contents of the field in ACL views and reports.

field_note

Optional. Specifies the text of a field note, which is added to the field definition in the table layout. The field_note parameter must be last, after all other required and optional parameters. The text of the note cannot be multiline. Quotation marks are not required.

Examples

a. Define a physical data field

The following examples define several different types of physical data fields.

DEFINE FIELD ProdDesc ASCII 12 24 AS "Product Description"

Defines a character field called “ProdDesc” that has a start position of 12, and a length of 24. The column title in the view is “Product Description”.

DEFINE FIELD QtyOH NUMERIC 61 5 0 PIC "(9,999,999)" AS "Quantity On Hand"

Defines a numeric field called “QtyOH” that has a start position of 61, a length of 5, and no decimal places. The column in the view uses the specified display format, and the column title in the view is “Quantity On Hand”.

DEFINE FIELD Transaction_date DATETIME 20 10 DD/MM/YYYY

DEFINE FIELD Transaction_date DATETIME 20 10 PIC "DD/MM/YYYY"

From source character data, both versions of the command define a datetime field called “Transaction_date” that has the specified date format in the source data, a start position of 20, and a length of 10. No column title is specified, so the column title defaults to using the field name.

DEFINE FIELD email_timestamp DATETIME 1 25 PIC "YYYY/MM/DD hh:mm:ss-hh:mm"

From source character data, defines a datetime field called “email_timestamp” that has the specified datetime format in the source data, a start position of 1, and a length of 25.

DEFINE FIELD Receipt_timestamp DATETIME 15 15 PIC "YYYYMMDD.hhmmss"

From source numeric data, defines a datetime field called “Receipt_timestamp” that has the specified datetime format in the source data, a start position of 15, and a length of 15.

DEFINE FIELD Receipt_timestamp PRINT 15 15 6 NDATETIME PIC "YYYYMMDD.hhmmss"

From source numeric data, defines a numeric field called “Receipt_timestamp” that has the specified datetime format in the source data, a start position of 15, a length of 15, and 6 decimal places. The NDATETIME parameter allows datetime values stored in the numeric field to be treated as datetime data.

b. Define a physical data field that reads mainframe Packed data

The following example uses the NDATETIME parameter to create a physical data field that reads date values from a Packed numeric field. ACL cannot recognize a date in a number that is compressed into fewer bytes than one per digit and that displays no date format. Consequently, you must “unpack” the number with NDATETIME to obtain the full number of digits, then specify the date format with the PIC parameter. To accurately indicate which numbers represent the day, the month, and the year, specify the same date format as the one in the Packed record layout.

DEFINE FIELD date_field_name NUMERIC 1 8 0 NDATETIME PIC "YYYYMMDD"



(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback