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

• You cannot perform a unique find on a work table. When finding records in a work table, you must use FIRST, LAST, NEXT, or PREV with the FIND statement, unless you are finding a record using its ROWID.
• You cannot define a field in a work table with the MEMPTR data type, but you can define a work table field as ROWID or RAW.
• You cannot define shared objects, work tables, or temp-tables within an internal procedure, a method in a class, or a user-defined function.
• ABL disregards the following options when used in conjunction with a work table:
  • The VALIDATE option on a DELETE statement
  • The SHARE-LOCK, EXCLUSIVE-LOCK, and NO-LOCK options used with the FIND or FOR statements
  • The NO-WAIT option on the FIND statement
• When you use the AMBIGUOUS function in conjunction with a work table, the function always returns a value of FALSE.
• Complete work table definitions must be included in a DEFINE SHARED WORK-TABLE statement and shared work tables must be defined identically.
• These are the differences between work tables and regular database tables:
  • The AVM does not use the OpenEdge database manager (and server for multi-user systems) when working with work tables.
  • If you do not explicitly delete the records in a work table, the AVM discards those records, and the work table, at the end of the procedure that initially defined the work table.
  • Users do not have access to each other's work tables.
• Because you cannot index a work table, the AVM uses the following rules for storing records in a work table:
  • If you create a series of work table records without doing any other record operations, the AVM orders the newly created records in the order they were entered.
  • If you use the FIND PREV statement at the beginning of a work table and then create a work table record, the AVM stores that record at the beginning of the work table.
  • When you use the FIND statement to find a work table record and then use the CREATE statement to create a new work table record, the AVM stores that new record after the record you just found.
• Data handling statements that cause the AVM to automatically start a transaction for a regular table will not cause the AVM to automatically start a transaction for a work table. To start a transaction for operations involving a work table, Use the TRANSACTION keyword.
• Work tables are private:
  • Even if two users define work tables with the same name, the work tables are private; one user cannot see records the other user has created.
  • If two procedures run by the same user define work tables with the same name, the AVM treats those work tables as two separate tables unless the SHARED option is included in both procedures.
• DEFINE SHARED WORK-TABLE does not automatically provide a shared buffer. If you want to use a shared buffer with a shared work table, you must define that buffer.
• Work table records are built in 64-byte sections. Approximately the first 60 bytes of each record are taken up by record specification information (or a record header). That is, if a record is 14 bytes long, it will be stored in two 64-byte sections, using the first 60 bytes as a record header. If the record is 80 bytes long, it will fit into three 64-byte sections. The first part contains 60 bytes of header information plus the first 4 bytes of the record. The second section contains 64 bytes of the record. And the last section contains the remaining record bytes.
• The NO-UNDO option in a work table definition overrides a transaction UNDO for CREATE, UPDATE, DELETE, and RELEASE statements accessing the work table, regardless of whether these statements are executed before or during the transaction block that is undone.
• A transaction UNDO overrides a FIND statement accessing a work table defined with the NO-UNDO option, regardless of whether the find is executed before or during the transaction that is undone.

  You should use the CASE-SENSITIVE option only when it is important to distinguish between uppercase and lowercase values entered for a character field. For example, use CASE SENSITIVE to define a field for a part number that contains mixed upper case and lowercase characters.

• A SHARED work table remains in scope for an instance of a persistent procedure until the instance is deleted. This is true even if the original procedure that defined the work table as NEW SHARED goes out of scope while the procedure instance remains persistent.

  If a trigger or internal procedure of a persistent procedure executes an external subprocedure that defines a SHARED work table, ABL includes the persistent procedure in the resolution of the corresponding NEW SHARED work table as though the procedure were on the procedure call stack.

• You cannot define a SHARED or NEW SHARED work table in a class definition (.cls) file. If you do, ABL generates a compilation error.
• You can specify a join between a temp-table or work table and any appropriate table using the OF keyword. The two tables must contain a commonly named field that participates in a unique index for at least one of the tables. For more information on table joins see the Record phrase reference entry.
• See OpenEdge Getting Started: ABL Essentials for information on work tables and temp-tables.

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