DEFINE WORK-TABLE statement

Defines a work table (a temp-table stored in memory) for use in one or more procedures, or within a single class.

This statement is supported only for backward compatibility. Use the DEFINE TEMP-TABLE statement, instead.

Syntax

DEFINE {[[ NEW ] SHARED ]|[ PRIVATE ]}
  { WORK-TABLE | WORKFILE }work-table-name[ NO-UNDO ]
  [ LIKE tablename[ VALIDATE ]]
  [ FIELD field-name { AS data-type | LIKE field}
    [field-options]]...
NEW SHARED { WORK-TABLE | WORKFILE }work-table-name
Defines and identifies a work table to be shared by a procedure called directly or indirectly by the current procedure. The called procedure must name the same work table in a DEFINE SHARED WORK-TABLE statement. The WORKFILE keyword is allowed only for backward compatibility; using WORK-TABLE or WORKFILE has the same effect.
SHARED { WORK-TABLE | WORKFILE }work-table-name
Defines and identifies a work table that was defined by another procedure that used the DEFINE NEW SHARED WORK-TABLE statement. The WORKFILE keyword is allowed only for backward compatibility; using WORK-TABLE or WORKFILE has the same effect.
[ PRIVATE ]{ WORK-TABLE | WORKFILE }work-table-name
Defines and identifies a work-table as a class-scoped object. A class-scoped handle-based object is not a member of a class, but provides a resource that is privately available to the class definition similar to a non-shared data element in a procedure definition. The option to specify the PRIVATE access mode is provided for readability. You cannot specify PRIVATE when defining a work-table as a data element in a method or procedure. The WORKFILE keyword is allowed only for backward compatibility; using WORK-TABLE or WORKFILE has the same effect.
Note: This option is applicable only when defining a class-scoped work-table in a class definition (.cls) file.
{ WORK-TABLE | WORKFILE }work-table-name
Defines and identifies a work table whose records you can access only within the current procedure, class, or method of a class.

The WORKFILE keyword is allowed only for backward compatibility; using WORK-TABLE or WORKFILE has the same effect.

NO-UNDO
Specifies that the AVM should not restore the record to its prior condition when a work table record is changed during a transaction and the transaction is undone. If you do not want the work table record undone even if it has changed during a transaction, use the NO-UNDO option with the DEFINE WORK-TABLE statement. NO-UNDO work tables are more efficient; use them whenever possible.
LIKE table-name
Indicates the name of a table whose characteristics you want to use for the work table you are defining. All of the fields in this base table are also in the work table. If you reference a database table with the LIKE option, the database containing that table must be connected at compile time. It need not be connected at run time.

If more than one connected database contains a table named table-name, you must qualify the table name with the database name. See the Record phrase description for more information.

HELP options are inherited from the table-name. Validate options are inherited only if the VALIDATE keyword is used.

VALIDATE
The work table fields inherit, from the dictionary, validation expressions and validation messages from the database table, table-name.
FIELD field-name
Identifies the name of a field in the work table.
AS data-type
Indicates the data type of the field or variable you are defining. The data types are CHARACTER, COM-HANDLE, DATE, DATETIME, DATETIME-TZ, DECIMAL, HANDLE, INT64, INTEGER, LOGICAL, RAW, RECID, and ROWID.

For more information on these data types, see the Data types reference entry.

LIKE field
Indicates the name of the variable, database field, temp-table field, or work table field whose characteristics you want to use for the work table field you are defining. If you name a variable with this option, you must have defined that variable earlier in the procedure. The work table field inherits the data type, extents, format, initial value, label, and column label of the field. You can override specific values by using the FORMAT, LABEL, INITIAL, DECIMALS, and EXTENT options. If you do not use these options, the field or variable takes on the characteristics of the variable or database field you name.

If you reference a database field in the LIKE option, the database containing that field must be connected at both compile time and run time. Therefore, use the LIKE option with caution.

field-options
Specifies options for the temp-table field. Any options you specify override any options inherited through the LIKE option. This is the syntax for field-options:
{  [ BGCOLOR expression]
   [ COLUMN-LABEL label]
   [ DCOLOR expression]
   [ DECIMALS n]
   [ EXTENT n]
   [ FONT expression]
   [ FGCOLOR expression]
   [ FORMAT string]
   [ INITIAL
       {constant|{ [ constant[ , constant]... ] }}]
   [ LABEL label[ , label]...]
   [ MOUSE-POINTER expression]
   [[ NOT ] CASE-SENSITIVE ]
   [ PFCOLOR expression]
   {[view-as-phrase]}}
Note: You cannot specify a BLOB field, a CLOB field, or an indeterminate array field in a work-table.

For a description of each option, see the DEFINE VARIABLE statement.

Example

The r-wrkfil.p procedure accumulates all balances by state and stores that information for display later. The procedure uses a work table to accomplish this task.

The r-wrkfil.p procedure defines the work table showsales. The work table contains the three fields named region, state, and tot-sales. These fields have all the same characteristics (except labels) as the Customer.SalesRegion, Customer.State, and Customer.Balance fields, respectively.

The first FOR EACH loop in the r-wrkfil.p procedure sorts Customers by state. Then it accumulates the balances for each Customer by state. When the procedure finds the last Customer in a state, it creates a showsales record for that state. The procedure assigns information to the fields in the showsales record. After looking at each Customer, the procedure continues to the next FOR EACH statement.

The second FOR EACH statement in the r-wrkfil.p procedure uses the information stored in the showsales table. Because you treat a work table within a procedure the same way you treat a database table, you can perform the same work with the showsales table that you can with a database table.

r-wrkfil.p

DEFINE WORK-TABLE showsales
  FIELD region    LIKE SaleRrep.Region LABEL "Region"
  FIELD state     LIKE Customer.State LABEL "St"
  FIELD tot-sales LIKE Customer.Balance COLUMN-LABEL "Total!Sales".

FOR EACH Customer, SalesRep OF Customer BREAK BY Customer.State:
  ACCUMULATE Customer.Balance (TOTAL BY Customer.State).
  IF LAST-OF(Customer.State) THEN DO:
    CREATE showsales.
    showsales.state = Customer.State.
    showsales.tot-sales = ACCUM TOTAL BY Customer.State Customer.Balance.
    showsales.region = SalesRep.Region.
  END.
END.

FOR EACH showsales BREAK BY showsales.region BY showsales.state:
  IF FIRST-OF(showsales.region) THEN
    DISPLAY showsales.region.
  DISPLAY showsales.state tot-sales (TOTAL BY showsales.region).
END.

Notes

See also

{ } Argument reference, { } Include file reference, Class-based data member access, CREATE statement, DEFINE BUFFER statement, DEFINE TEMP-TABLE statement, FIND statement, Format phrase, RUN statement