CHAPTER 4:  DATA DEFINITION LANGUAGE

The Data Definition Language (DDL) is used to described to ACCENT R in what general form the data appears and the fields defining the data.

SCHEMA DEFINITION STATEMENTS

PURPOSE:  The characteristics of the data are specified by the data definition statements of a Schema Definition (SD).  A Data Set (DS) cannot be created until its SD is first defined.  The comprehensive data definition statements allow detailed design of data tables.

Once an SD is defined and its data definition statements entered and stored in the DBL, it can be associated with any number of DS’s using the CREATE DS command.  This step assigns a disk file or table in which the information in the DS will be stored and creates a DBL entry for the DS.  The DBL entry includes the name of the disk file where the data is stored, the name of the associated SD, the date and time of creation, last update, and the number of records.  A DS whose data file or table already exists may also be associated with a SD in the DBL by using the ATTACH option with the CREATE DS command.

Schema Definition Statements

The next few pages of this chapter describe the statements that make up a Schema Definition (SD):

• Storage Form Statement

• Field Definition Statement

• Computed Field Statement

The DEFINE SD command initiates the process of creating a SD by assigning it a name and transferring control to the edit level.  At edit level, line-numbered statements are entered which describe the records and fields in a DS.  ACCENT R defaults to the TED/SLIM editor.  You may specify a different editor by setting the name of an editor you want to use in the systems field @EDITOR.  ACCENT R will invoke TED to save and compile files after other editors (if used) are exited.  The TED editor prompt is a double dash (--).

Once the definition is completed it can be compiled and saved in the DBL with the SAVE subcommand.  If errors are detected, the SAVE will return to the editor for you to make corrections.  If you want to save, the text without compiling it you can use the HOLD command.  This will create an entry in the DBL with the text and return to the editor.  The STOP subcommand can be used to exit the editor.

STORAGE FORM STATEMENT

PURPOSE:  The FORM statement specifies the mode of storage for records in the Data Set (DS) associated with the Schema Definition (SD).  This statement is optional, but when used should be the first statement in a SD.  If the statement is not specified then “FORM IS BINARY” is assumed.

The following table shows the different types of storage forms available in ACCENT R.  Storage forms associated with a specific computer platform will be described in the manual documenting the computer system or Data Base type.

Table 4-1 Form Statement Variations

NOTES:  For RMS and Oracle see the respective Gateway Manuals.

BINARY RECORDS

PURPOSE:  This version of the FORM statement allows data to be stored in binary, which is very efficient for processing.  Binary is the default ACCENT R storage form.  Binary records are word-oriented with each field beginning on a word boundary except on OpenVMS systems which are character oriented.  Binary Bit and Signed Bit fields may cross word boundaries; a bit fields begins immediately after the preceding field, and uses all bits, thus optimizing use of disk or memory space.

SYNTAX

FORM/IS/ BINARY [DISALLOW/IN PLACE DELETES/]

Example

*DEFINE SD BOOKS2_DBM2

--INSERT

00100  FORM IS BINARY DISALLOW IN PLACE DELETES

00105  CODE,INT,8

00110  PUB_CODE, CHAR,4

00120  END

SEE ALSO:  DELETE

ASCII RECORDS

PURPOSE:  This version of the FORM statement assumes all data is in a normal eight-bit character code.  It terminates each record by a carriage return and line feed, unless the NOCR option is specified.

SYNTAX

FORM/IS/ ASCII [NOCR]

Example

00010   FORM IS ASCII NOCR

00020   NAME,CHAR,30

00030   ADDR,CHAR,30

00040   AMOUNT,N,7,2

COMPRESSED RECORDS

PURPOSE:  ACCENT R’s compressed record formats, in most cases, substantially reduces the space required to store records.  Multiple record types may also be defined with this form because compression is on a record by record bases.  The compression algorithm looks for repeating characters.

SYNTAX

FORM/IS/ COMPRESSED

Examples

The examples show how record and disk file sizes are reduced when FORM IS COMPRESSED is used.

*LIST SD COMPRESSED

00010   FORM IS COMPRESS

00020   FIELD_C, CHAR, 50

00030   FIELD_I, INT, 3

*LIST SD UNCOMPRESSED

00020   FIELD_C, CHAR, 50

00030   FIELD_I, INT, 3

*USE DS COMPRESSED

*EXTRACT SHOW @RECORD_SIZE, " bytes, ", FIELD_C, FIELD_I

12 bytes, a1a1a   111

12 bytes, b2b2b   222

12 bytes, c3c3c   333

*USE DS UNCOMPRESSED

*EXTRACT SHOW @RECORD_SIZE, " bytes, ", FIELD_C, FIELD_I

54 bytes, a1a1a   111

54 bytes, b2b2b   222

54 bytes, c3c3c   333

*TYPE @FILE_SIZE("COMPRESSED.DS"), " bytes"

37 bytes

*TYPE @FILE_SIZE("UNCOMPRESSED.DS"), " bytes"

162 bytes

1. The commands ALTER, CHANGE, and UPDATE normally operate on records “in place.”  Records in a compressed data set may have their sequence altered in the following circumstances:

1. If a modified records new size is less than or equal to the original size then it retains the same position in the data set.

2. A modified record with a size larger than the original size when compressed is appended to the end of the data set with the original record marked as deleted.

2. The DELETE and PURGE commands mark the deleted records “in place”.  A data set can be repacked to eliminate deleted records by EXTRACTing the data set to itself (superseding the original table).

3. Using the SORT command with a TO clause will repack a data set with deleted records removed. SORTing the Data Set (DS) without the TO clause does not repack the data set if the records (excluding the skipped deleted records) are already in the specified sort sequence.

4. The system function @FILE_SIZE can help determine the effectiveness of the compression. Compare the data set size in uncompressed form with the data set size in compressed form.  Note that @FILE_SIZE displays the data set size in bytes.

5. The record field @ADDRESS can be used to determine if a modified record has been moved.  Save @ADDRESS before modifying the record, then compare this to its new position after modifying and rewriting the record.  If the two values are not the same, the record has been moved to the end of the data set and deleted at its current position.

LINED RECORDS

PURPOSE:  The purpose of LINED records is to provide a way to process text files as simple data records. LINED records may vary in length just as text lines vary in length.  All of the ASCII data types can be used with this form.

A LINED record is an ASCII record (sequential file organization with variable length records) that is acceptable to most text editors.  In OpenVMS a LINED record begins with a 16-bit binary character count of the number of characters in the record.

SYNTAX

FORM/IS/LINED

Example

A LINED record can be defined as a single field as in this example.

DEFINE SD NOTEBOOK

INSERT

00010  FORM IS LINED

00030  NOTES,CHAR,200

00040  END

--SAVE

1. The commands DELETE, ALTER, UPDATE, and PURGE do not work with LINED records since these commands operate “in place.”

2. If a LINED record exceeds its defined length, ACCENT R displays a warning message, but still writes the record.  Data beyond the defined length is ignored and ACCENT R advances to the next record.

3. If a LINED record is extracted to another Data Set (DS), it is extended to its maximum defined size.

4. If a DS with records that exceed the defined length is COPYed to another DS, the long records are not affected.  This happens because ACCENT R copies the table without looking at each specific record.

RESTRICTIONS:  An index may be defined for a LINED DS, but if the data is modified outside of ACCENT R without reforming the index before using it, you will get unpredictable results.

FIELD DEFINITION STATEMENTS

PURPOSE:  Each field of a Schema Definition (SD) is defined by a field definition statement.  Each field definition statement must contain the following basic information:

• Field Name

• Data Type (See Table 4 for data type listing)

• Field Width (required for all data types except dates)

A SD can contain up to 4000 field definition statements (although that number is slightly reduced if the field definitions contain some of the optional features).

SYNTAX

[$start_position] field_name, data_type,

[,{field_width; MAX} [, decimal_places]]

[,OCCURS m [{BY;*} [n {BY;*} o]] [{RECORD; COLUMN} /MAJOR/]]

[,IE = "input_edit_string"]

[,OE = "output edit string"]

[,PP = "print_picture_string"]

[,TITLE = "title_string"] [,\\ALIAS names\\]

[,OVERPUNCH]

[,/USAGE IS/ {ASCII; BINARY}]

[,USE VALUES \\field_value = "string"\\]

[,VALID conditional clause]

[READ ONLY]

[SWAP BYTES]

Data Types By Form Type

Table 4-2 Data Types

This table shows which can be used with the form type specified.

1. FORM IS BINARY

2. FORM IS ASCII

3. FORM IS COMPRESSED

4. FORM IS LINED

NOTES:  The DATA TYPE table describes what is supported within the Open Structured Query Language (OSQL) data base architecture of ACCENT R.  Either the data field type or data type abbreviation may be specified when defining a data element.

Refer to Appendix C for information on the magnitudes allowed and precision of the data types.  Refer to Appendix E for a discussion on numerical accuracy in ACCENT R.

Refer to Appendix F for notes on ACCENT R string handling.

Date fields do not accept a width specification.

The width for bit data types only defines storage.  The default print picture is that of an integer large enough to represent the value contained in the specified number of bits, (i.e., the number of digits in two **bits).  SB uses one extra position to display the sign or leading space.

When the data is stored in FORM IS BINARY, the width [,decimal, decimal_place] for numeric fields represents the default print picture only.  The width does not define the maximum number of digits allowed for the number since these numbers are stored in binary form.  For example, a field defined as 6, 2, can accept a large number such as 123456789.99, but to display this value, a large enough print picture must be specified, such as @"zzzzzzzzzz.zz".  Otherwise, the implicit print picture (as defined by width), will simply show as******.

There is no magnitude check on values entered or assigned for real and numeric fields.  If a maximum limit on a binary real or numeric field is to be imposed, use the VALID clause, such as VALID IF VALUE =< 999.99.

When numeric data is stored in ASCII, each decimal digit is stored as an eight-bit byte, so the defined width represents the maximum digits allowed for the data.  In this case, a field defined as six, two would limit the maximum value to 999.99.  With ASCII, the largest numeric value allowed is 99,999,999,999,999,999,999 (a width of 20 must be specified).

Expression editing can be done for Integer, Real, Numeric, and Date fields.  The Input Editing (IE) clause extension describes the data transformation that occurs whenever data is placed into a field.  This includes Data Set (DS) fields, Process Module (PM) DECLARE Section fields, or Global Storage (GS) fields.  This editing is the final step.  It represents the value that will be physically stored in memory or in a file.  As such, it is executed after any data validation (i.e. VALID IF clause).

The OE clause extension describes the data transformation that occurs when ever data is retrieved from a field and is the first step in data access.

Expressions used with the IE and OE clause extensions follow all the rules of ACCENT R's expression syntax.  The keyword VALUE is included.  It represents the stored value (for OE) or the ACCENT R context value (for IE) before the expression is evaluated.

The IE and OE clause extensions can be used for compatibility of scaled down data files created by other languages.  If a BASIC program was compiled on OpenVMS with the /SCALE = two option, the fields of that file can be described in ACCENT R with the example below.

FIELD1, N, 7, 2, IE=VALUE*100., OE=VALUE/100.

If FIELD1 has a value of one, the context value would be one and the stored value would be 100.  Although data type conversions are automatic, all components of an IE or OE expression should have the same data type for processing efficiency.  A period is used after the 100 to force a floating-point constant.

IE can be used to make sure stored values are rounded correctly.  Using the @RND function avoids most of the problems of storing decimal numbers in a binary format.

AMOUNT, N, 12, 2, IE=@RND(VALUE, 2)

Date/Time Value Limitations

1. The MIN and MAX inter-record functions do not preserve the time portions of extended date values.

2. The @DMIN and @DMAX functions do not preserve the time portions of extended date values.

3. Storing an extended date and time value into a D (DATE) or F (FULLDATE) field will cause loss of the time portion (which will be returned to 0:00:00 upon subsequent retrieval).  Use the DATETIME data type for retaining the extended dates.  The ACCENT R System Field @DATETIME occurs 25 times and can be used to hold extended date and time values.

4. A comparison of dates and times ignores the time portion for compatibility with existing applications.  New applications should use the results of @DATE_DIFF or @SECONDS_DIFF functions.

5. The CALENDRICAL relational operator is not cognizant of time specifications and thus will not succeed with a string such as "5/4/1993 12:00:00".

NOTES:  Length is not specified for DATE, FULLDATE or DATETIME type fields.

UNLESS and ORUNLESS can be used in place of IF and ORIF.

An example of using the IF clause is:

RATE,I,6,CV=IF PRICE   => 0 AND < 10 THEN .12 &

ORIF PRICE => 10 AND < 20 THEN .15 &

ELSE PRICE => 20 THEN .20

If specified conditions do not exist for an IF or UNLESS clause (and the ELSE option is missing), the default value of computed value fields will vary depending of the field type:

           "  "            for Alphabetic and Character type fields

              0            for Integer, Real, and Numeric type fields

@NODATE            for Data and FULLDATE type fields

Operands that may appear in the computed field clause are:  constants and literals; system functions; system fields; fields defined previously in the other computed value fields.  Items that are not allowed are:  inter-record functions (AVG, MIN, MAX STD_DEV, SUM); explicitly qualified fields (:T, :AUX, etc.); record fields (may be allowed for SD’s).

COMPUTED FIELD STATEMENT

PURPOSE:  Computed Fields add a means of defining virtual fields to the definitions of a Schema Definition (SD), Global Storage (GS), or the Declare Section of a Process Module (PM).  ACCENT R performs specified calculations on specified fields dynamically when a Computed Field is accessed.  The resulting values of the calculations do not take up space in a record or storage area. Computed Fields is are specified at the end of the regular field definitions in a GS, SD or PM Declare Section.  Computed fields are read-only.

SYNTAX

field_name, type [, width [, decimal_places]] [, PP = "picture"] {,CV;,CF;,COMPUTE} = clause

Example 1:  Figuring Discounted Sales

QTY,INT,6

PRICE,FLOAT,10,2

DISCOUNT,FLOAT,3

SALES,10,2, CV = QTY * PRICE - DISCOUNT/100 * (QTY * PRICE)

Example 2:  Figuring Current Age

BIRTH_DATE, DATE

AGE, INT, 3, CF = @IFIX((@DATE - BIRTH_DATE)/365.2425)

Example 3:  Figuring The Date For The First Tuesday Of The Next Month

MEETING, DATE, COMPUTE = @DATE + 0 EMONTHS +

(((9 - @WKDAY (@DATE + 0 EMONTHS) ) MOD 7) + 1) DAYS

1. The date of the last day of the current month is computed as a column in the following table.

@DATE + 0 EMONTHS

2. The weekday number of the last day of the month, column B, is subtracted from nine to set an offset for Tuesday as column C.

3. The result in step column C is divided by 7 (to assure the first week is used) and returns a remainder as column D.

4. One is added to the remainder giving the number of days to add to the last day of the month as column E.  The results are column F.

COBOL Packed Decimal Support

PURPOSE:  PACKDECIMAL has been added to support COBOL’s "comp-3" signed binary coded decimal (BCD) numbers.  At present, PACKDECIMAL fields are internally converted to/from a VAX D-Floating field type for input/output operations.  This limits the digit precision to 16 digits, but permits some applications to be prototyped and tested now.

Packed decimal data is defined using the keyword "PACKDECIMAL" or "PD".

SYNTAX

field_name, PACKDECIMAL, field_width [decimal_places] [other optional clauses defined earlier]

Example

The example that follows shows reading and writing of COBOL PACKDECIMAL data types with ACCENT R:

1. Two COBOL programs (1a. WRITER.COB, 1b. READER.COB) are created to write and read a data table with COMP-3 type fields;

2. The COBOL programs are compiled and LINKed for testing PACKDECIMAL;

3. The COBOL programs are run which create and then read the test data table with COMP-3 fields;

4. ACCENT R is initialized - Data Base Library (DBL) is created, Schema Definition (SD) is defined, data is extracted from the COBOL created data table conversion, Process Module (PM) is defined;

5. The COMP-3 fields are converted (ALTERed) into PACKDECIMAL fields;

6. The COBOL reading program lists the data from the altered data table.

Item 1a:    Text of COBOL source file WRITER.COB

IDENTIFICATION DIVISION.

PROGRAM-ID. WRITER.

ENVIRONMENT DIVISION.

INPUT-OUTPUT SECTION.

FILE-CONTROL.

SELECT OUTF, ASSIGN TO "SEC:", ORGANIZATION IS SEQUENTIAL.

DATA DIVISION.

FILE SECTION.

FDOUTF, VALUE OF ID IS "PACDEC.DS".

01TWO-FIELDS.

   02 N-FLD-1, PIC S9(5)V9(2), COMP-3.

   02 N-FLD-2, PIC S9(5)V9(2), COMP-3.

PROCEDURE DIVISION.

START-UP.

   OPEN OUTPUT OUTF.

   MOVE 1.23 TO N-FLD-1.

   MOVE 2.34 TO N-FLD-2.

   WRITE TWO-FIELDS.

   MOVE 3.45 TO N-FLD-1.

   MOVE 4.56 TO N-FLD-2.

   WRITE TWO-FIELDS.

   CLOSE OUTF.

   STOP RUN.

Item 1b:    Text of COBOL source file READER.COB

IDENTIFICATION DIVISION.

PROGRAM-ID. READER.

ENVIRONMENT DIVISION.

INPUT-OUTPUT SECTION.

FILE-CONTROL.

SELECT INFIL, ASSIGN TO "SEC:",ORGANIZATION ISSEQUENTIAL.

DATA DIVISION.

FILE SECTION.

FDINFIL, VALUE OF ID IS "PACDEC.DS".

01TWO-FIELDS.

   02 N-FLD-1, PIC S9(5)V9(2), COMP-3.

   02 N-FLD-2, PIC S9(5)V9(2), COMP-3.

PROCEDURE DIVISION.

START-UP.

   OPEN INPUT INFIL.

   READ INFIL AT END STOP RUN.

   MOVE N-FLD-1 TO DISP-ME. DISPLAY DISP-ME WITH NO ADVANCING.

   MOVE N-FLD-2 TO DISP-ME. DISPLAY DISP-ME.

   READ INFIL AT END STOP RUN.

   MOVE N-FLD-1 TO DISP-ME. DISPLAY DISP-ME WITH NO ADVANCING.

   MOVE N-FLD-2 TO DISP-ME. DISPLAY DISP-ME.

   CLOSE INFIL.

   STOP RUN.

Item 2:    COBOL Programs Compiled and LINKed

$ cobol WRITER

$ link/nodebug WRITER

$ cobol READER

$ link/nodeb READER

Item 3:    COBOL Programs Executed (write, read data table)

$ run WRITER

$ run READER

1.23  2.34

3.45  4.56

Item 4:    ACCENT R is initialized (DBL created, Schema Definition defined, Data Set created, data extracted from COBOL created data table, the conversion Process Module is defined).

$ ACCENT

ACCENT R, VAX/VMS Version 11.07 77 Copyright 1989 1996 by NIS, Inc.

*CREATE DBL PACDEC

*DEFINE SD PACDEC

--100   N_FLD_1, PACKDECIMAL,7,2

--110   N_FLD_2, PACKDECIMAL,7,2

--SAVE

*CREATE DS PACDEC SD PACDEC ATTACH

*USE DS PACDEC

*EXTRACT

1.23   2.34

3.45   4.56

*DEFINE PM PACDEC

-—100   CONTROL SECTION

-—110   RELATE DS PACDEC AS MASTER FOR UPDATE

-—120   DETAIL SECTION

-—130      TYPE 6s,"N_FLD_1 N_FLD_2"

-—140      TYPE "In:  ", N_FLD_1, 4s, N_FLD_2

-—150      INCR N_FLD_1 BY @SQRT(@RECORD)

-—160      INCR N_FLD_2 BY @RECORD ^ 2

-—170      TYPE "Out:  ", N_FLD_1, 4s, N_FLD_2

--SAVE

Item 5:    COMP-3 fields converted (ALTERed) to PACKDECIMAL

The ALTER command changes the values of Data Set (DS) fields.  The VIA clause calls for a Process Module (PM) named PACDEC to do the work (as opposed to specifying the change in the command itself).  The PM adds the square root of the record number to the first field.  The value of the record number squared (to the second power) is added to the second field.

*ALTER VIA PACDEC

        N_FLD_1   N_FLD_2

In:     1.23      2.34

Out:    2.23      3.34

       N_FLD_1   N_FLD_2

In:     3.45      4.56

Out:    4.86      8.56

*quit

Item 6:    COBOL program reads altered data table

$ run READER

2.23   3.34

4.86   8.56

NOTES:  The "field_width" must exactly match the total number of digits as would be specified in a  COBOL program, and is the sum of the count of integer and decimal digits.

For example, the COBOL description:  02 FLD, PIC S9(5)V9(2), and COMP-3. is specified to ACCENT R as:  FLD, PACKDECIMAL, 7, 2.

SECONDARY FIELDS

PURPOSE:  A secondary field is a field that redefining components of a previously described primary field.  A secondary field provides a means of re-identifying the primary field.  This permits access to be gained to parts of the primary field or the entire field value.  Secondary fields are useful in allowing parts (or all) of the primary field to be operated on and assigned further characteristics.  Secondary fields can have all of the options that primary fields can, except that the OCCURS clause is limited to a single dimension.

Secondary fields are defined on the lines immediately following the primary field description.  The syntax of a secondary field differs from that of a primary field only in that its name must be preceded by a dollar sign ($) and an integer.  The integer specifies the starting position of the secondary field within the primary field.  The starting position is assumed to be in characters for ASCII data, in bits for binary data.

The name can be proceeded by $$ as well.  The purpose of this is described later in this section.  Basically it to overrides data type checking between the secondary and primary descriptions.

Example

CODE,CHAR,7

$1 DEPT,CHAR,1

$2 VENDOR,CHAR,4

$6 SCODE,I,2,USAGE IS ASCII

The DEPT contains only the first character of CODE.  The VENDOR consists of the second, third, fourth, and fifth characters of CODE. SCODE contains the sixth and seventh characters of CODE.  These three secondary fields now allow access to components of the CODE field such that department, vendor, or subcode information can be easily obtained when appropriate.  Secondary fields can be defined to overlap each other, can have different names and identical descriptions, or can include all characters of the primary field just to redefine the data type.

Note that the description for SCODE specifies ASCII Integer data while its primary field is defined as Character data.  This particular difference is allowable because the character data includes ASCII integers.

The following table summarizes the compatibility of data types between primary and secondary fields.  It indicates which primary fields of the given data type can have a secondary field of the given type.  If a secondary field is defined whose data type is incompatible with its primary field, ACCENT R displays an error message and the line containing the error to indicate exactly where the error occurred.  The storage format (BINARY or ASCII) of data in a secondary field must be the same as for the primary field.  Thus in the example above the integer subfield SCODE must be defined as ASCII because the Character data type of the primary field is ASCII by default.

DATA TYPE Compatibility in PRIMARY & SECONDARY Fields

1. When storage format is ASCII table 4-3a

table 4-3a

2. When storage format is binary table 4-3b

table 4-3b

O - means that the subfield must redefine the complete field.

X - means that the subfield can redefine part or all of the primary field.

When data is entered to a binary Data Set (DS), any field left blank contains nulls.  This has special significance if data is entered to secondary fields instead of primary fields.  When ACCENT R retrieves character data, it assumes that a field ends when a null is encountered (or is empty if the first character is a null).  Therefore, if data is entered to secondary fields, and if the secondary fields leave gaps in the primary field, the nulls left in the primary field will cause ACCENT R to see an empty or truncated field when the primary field is addressed.  Consider the following example:

00010  FORM IS BINARY

00020  PRI,CHAR,7,ASCII

00030  $2 SEC1,CHAR,2,ASCII

If 'AA' is entered into SEC1, and nothing into PRI then when accessing the field PRI, ACCENT R will encounter a null at the first position, and therefore will see the field PRI as empty and returns an empty string.

This situation can be avoided by first setting PRI to spaces, since ACCENT R sees spaces as valid field characters.

SECONDARY FIELDS and OCCURS Clauses

A single dimension OCCURS clause is allowed in a secondary field and/or a primary field having secondary fields.  Each occurrence of the primary field contains all secondary fields associated with it.  For example,

PRIM_FLD,CHAR,2,OCCURS 3

$1 SEC_FLD1,CHAR,1

$2 SEC_FLD2,CHAR,1

The occurrences of the primary and secondary fields in the above example can be visualized in the following table.

table 4-3c

A secondary field is addressed exactly like a primary field by field name with the occurrence number in parentheses.  The third occurrence of SEC_FLD2 would thus be referenced as SEC_FLD2 (3).  A secondary field cannot contain an OCCURS clause that addresses a greater number of characters than are contained in a single occurrence of its primary field.  The following example table 4-3d and representation illustrate the relationship between a primary field and two secondary fields that contain OCCURS clauses.

00010  PRI, CHAR, 20

00020  $1 SEC1, CHAR, 4, OCCURS 5

00030  $1 SEC2, CHAR, 1, OCCURS 3

table 4-3d

Example

TYPE PRI (IS THE SAME AS)

TYPE SEC1(1), SEC1(2), SEC1(3), SEC1(4), SEC1(5)

The next example table 4-3 incorporates an OCCURS clause on both the primary and secondary fields.  Addressing a secondary field in this case requires enclosing in parentheses the occurrence number of the primary field, a comma, and the occurrence number of the secondary field.

PRI, CHAR, 3, OCCURS 4

$2 SEC, CHAR, 1, OCCURS 2

table 4-3

Example

TYPE PRI(1), PRI(4), SEC(1,2), SEC(4,1), SEC(4,2)

Position X would be addressed as SEC (3,2).  Note that each occurrence of the primary field contains the secondary field and its occurrences.

Secondary Fields and Validation Clauses

Validation clauses on secondary fields are permitted.  ACCENT R checks entered data only against the VALID clause of the field into which the data is entered.  Data entered directly into a secondary field is not checked against any validation clauses of the primary field.  Data entered into a primary field is not checked against any validation clauses of a secondary field.

Redefining Fields with the $$ Option

A field can be redefined any number of times by preceding a subsequent definition with two dollar signs ($$).  This option allows the new definition to redefine part of or the entire space defined by all occurrences of the first definition.  The new field definition cannot, however, address more characters than the total space used by the primary field definition.  For example,

00010   FIRST_NAME,CHAR,7

00020   $$1 SECOND_NAME,FLOAT,7,ASCII

Line 20 in effect redefines the area of the first field.  Thus either Alphabetic or Numeric (FLOAT) data could be stored in the field, depending on which name is used to reference it.  There is no check of data or definition between the primary field and any new definition.  The new definition is totally independent of the primary definition.

Example

      FLD1,   CHAR,    100

$$1   FLD2,   INT,       7

$$1   FLD3,   BIT,      16

$$9   FLD4,   FLOAT,   9,3

Repeating Group Statements

PURPOSE:  The GROUP statements allow group of fields in a Schema Definition to be repeated any number of times.  This feature is useful when a portion of a record will contain multiple values.  For example, student records would ideally have a field for the student’s name and fields for courses, instructors, and grades that are repeated several times to accommodate the information on each class a student is taking.

GENERAL FORM

GROUP group.name OCCURS n /TIMES/

.

.

.

GROUP END

where:

GROUP group.name OCCURS n /TIMES/ begins a group of field definitions that are to be repeated a specified number of times.

group.name is the user-assigned name of the group of field definitions to be repeated.  Conventions for group names are the same as for field names (see Chapter 2).  The group name is used when entering data to the fields through the ENTER, LOAD, or CHANGE command.

n specifies the number of times that the group of field definitions is to be repeated.  There is no maximum for the value that is assigned to n if the total number of characters in the record does not exceed the maximum record length and can be 1.

GROUP END marks the end of the fields that are to be included in the repeating group.

All field definition statements that appear between the GROUP and GROUP END statements are included in the group.  Fields in the group are addressed by including the occurrence number in parentheses immediately after the field name:

field.name(n)

NOTES:  A field definition statement in the repeating group may contain at most a single dimension OCCURS clause.  Addressing a grouped field which itself contains an OCCURS clause requires enclosing in parentheses the occurrence number of the field.

Secondary fields are not permitted in field definition statements that are grouped.

A GROUP may be defined only within a single record type of a Multiple Record Type Schema Definition.

Groups may not be nested.

Example

*DEFINE SD EARNINGS

--10   NAME,C,20

--20   SALPER.NO,I,3

--30   GROUP SALES OCCURS 12 TIMES

--40     MO.SALES,N,8,2

--50     MO.COMMISSION,N,7,2

--60     MO.GROSS.PAY,N,8,2

--70   GROUP END

—-SAVE

The GROUP statement in the above example states that each of the field definition on lines 40, 50, and 60 is repeated 12 times.  Using the GROUP option in this Schema Definition enables data to be stored in three fields, each 12 times once for every month of the year.

In the following example, the individual fields of a repeating group also contain OCCURS clauses.  The total number of character positions generated from this group of field definition statements would be 72:

GROUP STUDENT   OCCURS 3 TIMES

   CLASS,C,4,   OCCURS 4    4 X 4 = 16

   GRADE,I,2,   OCCURS 4    2 X 4 =  8

GROUP END

                                    24 X 3 = 72

The second occurrence in the third group of the field GRADE would be addressed as: GRADE(3,2).

MULTIPLE RECORD TYPE STATEMENTS

PURPOSE:  The RECORD ID and STRUCTURE statements are used to describe different kinds of records that are to be stored together within one Data Set.  Up to 511 different record types are allowed in one SD.  This option greatly enhances the flexibility of the ACCENT R system by allowing a variety of record formats in one Data Set.

GENERAL FORM

RECORD ID IS field.name

STRUCTURE FOR record.name RECORD ID = \\rec.id.values\\

where:

RECORD ID IS field.name specifies a field common to all of the record types in the Schema Definition.  This statement appears only once, and must precede the first STRUCTURE statement.  The value that later appears in this field determines the type of each record.  The relationship between the field value and the record type is established in the STRUCTURE statement(s), which introduces each record type.  The RECORD ID field does not have to be the first field, but it must be a primary field appearing in the same position in all records of the Data Set. The field may not have an OCCURS clause or be part of a GROUP.

Structure for record.name record id = \\rec.id.values\\ must be the first statement in each set of statements that defines a different record type.  It gives a name to the record that is described by the following field definition statements and tells what value will appear in the record id field to identify this kind of record.  At least one STRUCTURE statement must be included in the Schema Definition to assign a record type if a RECORD ID statement has been used.

record.name is the name that identifies the record type defined by the immediately following field definition statements.  When the STRUCTURE command is given, this name will be printed as a header for the fields of each different record type.  The record name is also used in the SHOWING clause and in the FIELDS clause of the ENTER and CHANGE commands.  It can also be accessed through the System Field @RN (see Chapter 11).

\\rec.id.values\\ specifies one or more values which, when they later appear in the field specified as the RECORD ID field, identify the type of the record.  The values must be consistent with the field’s data type:  a string enclosed in delimiters for C or A type, integers for I, etc.  ACCENT R does not check the rec.id.values against the field data type at compile time.  Incompatible data types will generate errors at run time.  You should ensure that the same rec.id.value is not used to identify different record types.  This will also generate errors at run time.

NOTES:  Groups can be included in multiple record type Schema Definitions, but only within a single record type.

Multiple record types Data Sets cannot be converted to a new format by the CONVERT command or the SDMOD utility.  However, it is very easy to write a Process Module to handle such a change.  See the example in chapter 13 under “Using Process Modules”.

Nonexistent Fields.  When you are defining a Schema Definition for multiple record types, you most likely will define fields in some record types that are not defined for other record types.  Fields that do not occur in all record types are called nonexistent fields.  When sorting, indexing, or matching Data Sets, ACCENT R normally treats a record that has a nonexistent key field as if that field were present and contained a value equal to the same field.  In the last record processed, that actually contained that field.

Equally important, a special ACCENT R feature allows you to change this assumption.  Specifically, you can include a less than (<) or greater than (>) sign after a field name in the following places:

In the key field list when describing a domain of a DI.

In the list of fields in a SORT command.

In the MATCH ON or MATCH BY clause of a join command.

Then during processing, if a key field followed by a less than sign (<) is nonexistent in a record, ACCENT R assumes the smallest possible value for that field.  Similarly, the greater than sign (>) causes ACCENT R to assume the largest possible value for that field.

The second example below shows the impact of nonexistent fields on a SORT operation, and the result when the “<” and “>” signs are used.  Bear in mind that the same principle applies to matching and indexing.

Examples

*LIST SD ORDERS

00010  RECORD ID IS RT

00020  !FIRST RECORD TYPE

00030  STRUCTURE FOR CUSTOMER RECORD ID=’C’

00040  RT,A,1

00050  ORDER.NO,I,6

00060  CUST.NO,C,6

00070  DATE,D

00080  REQ.DEL.DATE,D

00090  SHIP.TO.ADDRESS,C,25

00100  !SECOND RECORD TYPE

00110  STRUCTURE FOR ORDER RECORD ID=’0’

00120  RT

00130  ORDER.NO

00140  PROD.CODE,C,6

00150  QTY,I,5

The above Schema Definition uses multiple record types to simplify the recording of customer orders.  When a customer places an order for more than one product at the same time, much duplication of information can be avoided by segregating the order information into two record types.  general information on orders will be contained in records that contain the value “C” in the RT field.  Those records are referred to as "CUSTOMER" records as stated in line 30.  They will record the order number, the customer number, and the date of the order, the requested delivery date, and the shipping address for each customer order.  The product identification and quantity ordered will be stored in records that have an “O” value in the RT field.  Those records are referred to as “ORDER” records as needed for each “CUSTOMER” record so you do not have to reenter and duplicate storage of the general information.

One way that you can review the organization of the Schema Definition is with the STRUCTURE command:

*STRUCTURE FOR SD ORDERS

CUSTOMER

         RT A 1

         ORDER.NO I 6

         CUST.NO C 6

         DATE D 6

         REQ.DEL.DATE D 6

         SHIP.TO.ADDRESS C 25

ORDER

         RE A 1

         ORDER.NO I 6

         PROD.CODE C 6

         QTY I 5

The Schema Definition listed below describes the records to be sorted in the following illustration of nonexistent fields.

*LIST SD EXAMPLE

0010  RECORD ID IS W

0015  STRUCTURE FOR ABC RECORD ID =”1”

0017  W,C,1

0020  X,C,5

0030  Y,I,3

0040  Z,I,2

0050  STRUCTURE FOR XYZ RECORD ID =”9”

0055  W

0060  X

0070  Z

The results of three different SORT commands on the same unsorted records appear below.  The symbol “-“ indicates a nonexistent key field.

Consider the results in the third and fourth columns.  Note how the records with nonexistent Y fields (the record type 9 records) are sorted according to the less than (<) or greater than (>) sign specified.

A Data Index having domains that parallel the SORT commands shown above is:

00100  INDEX TYPE IS RAM

00110  INDEX TO EXAMPLE

00120  DOMAIN Y ON Y

00130  DOMAIN XYZ ON X,Y<,Z

00140  DOMAIN XLITTLEYZ ON X,Y<,Z

00150  DOMAIN XBIGYZ ON X,Y>,Z

If you want to omit records that do not contain the field Y, the domain statement could include the following ENTER clause:

ENTER UNLESS W = “9”

BLOCKED RECORDS

RECORDS /ARE/ BLOCKED n /RECORDS PER BLOCK/

The number of records that can be blocked (n) ranges from a minimum of 2 to a maximum of 65,535.  Records can be blocked if the SD form is ASCII, ASCIINOCR, or BINARY.  It is not valid for SD forms of RMS INDEXED, RMS RELATIVE, COMPRESS, or LINED.

FORMATS and POSITIONING

Many ACCENT R commands display records, fields, functions, expressions, and literals at the terminal or on a file.  If a format is not specified, ACCENT R displays each object according to a default format.  It is possible, however, to produce output in virtually any format desired by using one of the three picture formats: numeric, character, or free-form.  A related feature, position control, allows the location of an object to be specified on a line and the placement of a line on a page.

This section describes default formats first, then presents the position control and picture format capabilities.

DEFAULT FORMATS

The following table summarizes the default formats that will be used to output data if a format is not specified in the field definition or in the output statement or command.  String values are left justified within the format.  Integer values are right justified.  Numeric values are aligned on the decimal point.

Table 4-4 Default Formats

POSITION CONTROL

Special literals, a System Field, and a line control character are available for use in positioning text in a line or on a page.  The following table summarizes these features:

Table 4-5 Position Control

Example

This example instructs EXTRACT to display from each record the value of field ORDNUM, 10 spaces, the value of field QTY, a carriage return-line feed, then 25 spaces, the field ORD_DATE, and the final automatic carriage return-line feed.

*EXTRACT SHOWING ORDNUM, 10B, QTY, @CR, 25B, ORD_DATE

In the next example, the PRINT statements instructs ACCENT R to move to position 10, display the words TITLE and PAGE and the NUM value with the specified spacing, then leave two blank lines, display the text PUBLISHER: ,:, and leave the carriage at its current position on the line.

100  PRINT 9B, "TITLE",8B,"PAGE ",NUM,@CR,@CR

110  PRINT "PUBLISHER:  ",NOCR

Several positioning statements, SKIP, EJECT, TAB, CENTER, PRINT, and TURN, are available only in Process Modules that create reports.

PICTURE FORMATS

Picture formats enable an object to be written in a format different from its default or stored format.  Pictures can be used in any command or statement that displays items to a file or to the terminal.  Picture formats can also be used in the print picture clause of a field definition, and in the @STR function.

A picture format specifies the desired output format with characters enclosed in quotation marks; for example, “ZZZ.DD”.  Each format character corresponds to one position.  If a format character occurs several times in succession, the format can be shortened by specifying the character only once, but preceding it with an integer that tells the number of times the character is to be repeated.  For example, “3Z.2Z” is the same as “ZZZ.ZZ”.

The following paragraphs summarize the forms for print pictures in all allowed contexts.  Examples of each follow on the next page.

SYNTAX

In a Field Definition:    PP = "format"

In a SHOWING Clause:    item @"format"

In the @STR System Function:    @STR (ae [,"format"])

NUMERIC PICTURE FORMATS

Numeric picture formats consist of format characters for integer digit positions and, optionally, a decimal point or decimal point indicator and format characters for decimal digit positions.  All decimal values will be rounded to fit the number of decimal places specified.

With one exception, these characters cannot be mixed in one format.  The chosen character must appear at every digit position, both to the left and right of the decimal point.  The exception is “D”, which can occur to the right of the decimal point when another formatting characters to the left of the decimal point.  See examples below.  The numeric format characters are shown in the following table.

Table 4-6 Numeric Format Characters

Example

The first example shows the values displayed in default format.

*EXTRACT SHOW NUMB

 -4.50

 .00

 4.50

 340.49

*EXTRACT SHOW NUMB @"7D"

*******

0000000

0000005

0000340

*EXTRACT SHOW NUMB @"7Z"

-5

5

340

*EXTRACT SHOW NUMB @"7M"

 -5

 -

 5

 340

*EXTRACT SHOW NUMB @"7*"

 *****-5

 *******

 ******5

****340

*EXTRACT SHOW NUMB @"7+"

 -5

 +0

 +5

 +340

*EXTRACT SHOW NUMB @"DDD.DD"

 ******

 000.00

 004.50

 340.49

*EXTRACT SHOW NUMB @"$$$$.DD"

 *******

 $.00

 $4.50

 $340.49

*EXTRACT SHOW NUMB @"7-.--"

 -4.50

 .00

 4.50

 340.49

NOTES:  If the value is too large for the specified picture format, ACCENT R displays an asterisk (*) for each format character instead of displaying the number.  For example, 12479 @ “ZZZZ” would display as ****.

PRINT PICTURE - E

PURPOSE:  The “E” print picture numeric format character is used to print or display numeric values in scientific exponential notation.  The “E” character has the following features:

• One or more digits may come before a decimal point.  The printed or displayed number is automatically scaled according to the format specification.

• Digits after the decimal point and the decimal point itself can be suppressed during printing or display.

• Rounding is done up to the least significant digit.

• Spacing formula for the width of exponential displayed fields:  1: 1.  Space is reserved for the sign (+ or -), 1 space is used for the decimal point.  (2 if a zero is included), add the number of spaces that equal the number of digits which will be placed before and/or after the decimal point, 4 spaces are used for the exponent.

• The exponent form is E+DD or E-DD, where DD is two-digit integer with a leading zero.

SYNTAX

Examples

The table below shows the input value, the print picture and the resulting value using different "E" formats.  The two examples below show how the "E" could be used with TYPE commands.

*TYPE -123.456@"E-D.DDDD"

-1.2346E+02

*TYPE 123.456@"E-3D"

123E+00

NUMERIC FORMAT MODIFIERS

Modifiers can be used in a picture format to further specify the form of the output.  Any modifier must be specified in the picture format exactly where it is to occur in the output.  The format modifiers are listed in Table 4-7.  Examples are listed in Table 8g - h.

Table 4-7 Numeric Format Modifiers

Table 4-8a Format Modifier Examples

Table 4-8b Format Modifier Examples

Table 4-8c Format Modifier Examples

Table 4-8d Format Modifier Examples

Table 4-8e Format Modifier Examples

Table 4-8f Format Modifier Examples

Table 4-8g Format Modifier Examples

Table 4-8h Format Modifier Examples

CHARACTER PICTURE FORMATS

Character picture formats are used to specify any special output form for character data.  Character picture formats contain only X’s and instruct ACCENT R to display one character for each X in the format.  If the data contains more characters than the specified picture format, ACCENT R truncates the rightmost characters on output.

Example

FREE FORMAT PICTURES

Free format pictures are specified as "", that is, two quotation marks enclosing nothing.  Numbers that are written in free format occupy as many positions as needed with no leading zeros and no trailing zeros to the right of the decimal point.  Character data written in free format will have trailing blanks removed.

Example

*TYPE 123.50 @"","NOSPACE"

123.5NOSPACE

*TYPE 123.50 @"",1B,"ONESPACE"

123.5 ONESPACE

Free format output of character fields is particularly useful for displaying name concatenations.  For example, assume the fields LNAME and FNAME are defined as:

LNAME, ALPHABETIC, 20

FNAME, ALPHABETIC, 15

The EXTRACT commands below show the difference between free format and the default output format.

*EXTRACT IF NUM=7 SHOW FNAME,LNAME

Betty     Anderson

*EXTRACT IF NUM=7 SHOW FNAME @"",1B,LNAME @""

Betty    Anderson