Progress
Language Reference
DEFINE TEMP-TABLE Statement
Defines a temporary table. Progress stores temporary tables on disk in a temporary database.
A temporary table can be either global (lasting for the entire Progress session) or local (lasting only as long as the procedure that creates it), and either shared (visible to other procedures that want to access it) or nonshared (visible just to the procedure that created it).SYNTAX
NEW SHARED
Establishes a shared temporary table.
The scope of a shared temporary table is the scope of the procedure that established it.
NOTE: Progress might establish multiple shared temporary tables with the same name in the same Progress session.SHARED
Communicates the desire of a procedure to access an established shared temporary table.
For a procedure to access an established shared temporary table, the procedure must define it using the SHARED option, and must be called by any procedure that can access it, which might be the procedure that established it.
NEW GLOBAL SHARED
Establishes a global shared temporary table, and accesses an existing one.
The scope of a global shared temporary table is the Progress session.
The first procedure to define a temporary table NEW GLOBAL SHARED establishes it. Subsequent procedures access it.
NOTE: Progress does not establish multiple global shared temporary tables with the same name in the same Progress session.TEMP-TABLE temp-table-name
Indicates the name of the temporary table. The procedure that establishes the temporary table determines the name. The procedures that share the temporary table use that name to identify it.
NO-UNDO
Specifies that when a transaction is undone, changes to the temporary table records need not be undone. If you do not specify this option, all records in the temporary table are restored to their prior condition when a transaction is undone. The NO-UNDO option can significantly increase the performance for temporary table updates; use it whenever possible.
LIKE table-name [ USE-INDEX index-name [ AS PRIMARY ] ] . . .
Specifies the name of a table whose characteristics the temporary table inherits. All field definitions of table-name are added to the temporary table. table-name can represent a database table or another temporary table. At compile time, the database of any database table must be connected.
HELP options are inherited from the table-name. Validate options are inherited only if the VALIDATE keyword is used.
Some index definitions from the specified table might also be added to the temporary table:
- If you use the USE-INDEX option, only the definitions of indexes you specify with that option are copied to the temporary table. If one of these indexes is the primary index of the LIKE table, it becomes the default primary index of the temporary table. You can, however, use the AS PRIMARY option to override this default primary index.
For example, to make the index country-post the primary index (thereby, overriding the default primary index cust-num in the table customer), you specify it as follows:
- If you do not specify the USE-INDEX option and do not use the INDEX option of the DEFINE TEMP-TABLE statement, then all index definitions are copied from the specified table to the temporary table. In this case, the primary index of the specified table becomes the primary index of the temporary table.
- If you do not specify the USE-INDEX option but do use the INDEX option of the DEFINE TEMP-TABLE statement, then no indexes are copied from the specified table.
- Progress does not copy inactive indexes to the temporary table.
RCODE-INFORMATION
Determines whether Progress stores temp-table buffer-field actual attributes in the r-code’s initial value segment (IVS), which has a maximum size.
If you specify this option, Progress stores temp-table buffer-field actual attributes in the IVS at compile time, and provides actual attributes at run time.
If you do not specify this option, Progress stores no temp-table buffer-field attributes in the IVS at compile time, and provides default attributes at run time. Also at run time, if you access the FORMAT, INITIAL, LABEL, or HELP attribute, Progress issues a warning message that it is supplying a default attribute. To suppress the warning, use the NO-ERROR option in the statement that accesses the attribute.
NOTE: The RCODE-INFORMATION option of the DEFINE TEMP-TABLE statement prevents the IVS from exceeding its maximum size, which prevents the program from compiling. Using this option judiciously-that is, only for temp-tables for which you must access buffer-field actual attributes-can help keep the IVS within its size limit. For more information on the IVS, see the “R-code Features and Functions” appendix of the Progress Programming Handbook .FIELD field-name
Defines a field in the temporary table. You can use FIELD clauses with the LIKE option to define additional fields for the temporary table, or you can define all your fields with FIELD clauses.
AS data-type
Specifies the data type of the field. The valid data types are CHARACTER, DATE, DECIMAL, HANDLE, INTEGER, LOGICAL, RECID, ROWID and WIDGET-HANDLE.
LIKE field
Specifies a database field or a variable whose characteristics the temporary table field inherits. If you name a variable with this option, that variable must have been defined earlier in the procedure. The temporary table field inherits the data type, extents, format, initial value, label, and column label. You can override selected characteristics of the field or variable with the field-options parameter.
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.
VALIDATE
The temp-table fields inherit, from the dictionary, validation expressions and validation messages from the database table, table-name.
field-options
Specifies options for the temporary table field. Any options you specify override any options inherited through the LIKE option. This is the syntax for field-options.
HELP
A quoted CHARACTER string that represents the help text.
For a description of the other options, see the DEFINE VARIABLE Statement.
INDEX index-name [ IS [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ]
Defines an index on the temporary table. To define a unique index, specify the UNIQUE option. To define the primary index, specify the PRIMARY option. To define a word-index, specify the WORD-INDEX option.
If you define more that one index on the temporary table, you can specify PRIMARY for none or one of the indexes. If you specify PRIMARY for none of the indexes, Progress makes the first index you specify the primary index.
If you define no indexes on the temporary table, and the temporary table does not inherit the indexes of another table through the LIKE option of the DEFINE TEMP-TABLE statement, Progress creates a default index, makes it the primary index, and sorts the records in entry order.
index-field [ ASCENDING | DESCENDING ]
Specifies a temporary table field to use as a component of the index. You can use the ASCENDING or DESCENDING option to specify that the component has ascending or descending order.
If you do not specify a sort orientation (ASCENDING or DESCENDING), the index component gets the sort orientation of the previous index component, or, if there is no previous index component, ASCENDING.
NOTE: This rule applies only to index components of temp-tables.For example, the following two temp-table definitions are equivalent:
The following two temp-table definitions are also equivalent:
EXAMPLES
The following procedure creates a temporary table (tempitem) that stores the total inventory value (item.price * item.on-hand) for each catalog page (item.cat-page) in the sports database. It builds temp-item with two indexes-one that sorts the table in ascending order by catalog page and a second that sorts the table in descending order by inventory value.
After building temp-item, the procedure displays a dialog box that prompts for report parameters. These parameters include the cutoff value of catalog page inventory to report, and whether to display the report by catalog page (ascending) or inventory value (descending). After displaying the report, the procedure displays another dialog box to repeat the process. The process is repeated until you press the CANCEL button. This procedure shows how you can use a temporary table to store a calculated result from the database, and efficiently report the same result according to different sorting and selection criteria.
The following example extracts a temp-table buffer-field attribute, letting you include and exclude the RCODE-INFORMATION option of the DEFINE TEMP-TABLE statement and the NO-ERROR option of the assignment statement that accesses the temp-table buffer-field attribute. The comment at the top of the example describes the results.
NOTES
- If you define a temporary table LIKE a database table, the temporary table does not inherit the database table’s database triggers.
- You cannot define a temporary table field of type MEMPTR.
- You cannot define shared objects, work tables, or temporary tables within an internal procedure or a user-defined function.
- Progress disregards the following when used in conjunction with a temporary table:
- Data handling statements that cause Progress to automatically start a transaction for a regular table will not cause Progress to automatically start a transaction for a temporary table. If you want to start a transaction for operations involving a temporary table, you must explicitly start a transaction by using the TRANSACTION keyword.
- 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 temporary 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 temporary 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 temporary table, Progress includes the persistent procedure in the resolution of the corresponding NEW SHARED temporary table as though the procedure were on the procedure call stack.
- You can specify a join between a temporary 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.
- If you define a temporary table with the same name as a database table and then you define a buffer for that name, the buffer will be associated with the database table, not with the temporary table.
- See the Progress Programming Handbook for information on temporary tables and work tables.
SEE ALSO
CREATE TEMP-TABLE Statement, DEFINE WORK-TABLE Statement, RUN Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |