Progress
Language Reference


DEFINE TEMP-TABLE Statement

Interfaces
OS
SpeedScript
All
All
Yes

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

DEFINE [ [ NEW [ GLOBAL ] ] SHARED ] TEMP-TABLE temp-table-name
  [ NO-UNDO ] 
  [ LIKE table-name
      [ VALIDATE ]
      [ USE-INDEX index-name [ AS PRIMARY ] ] ...
  ]
  [ RCODE-INFORMATION ] 
  [ FIELD field-name
      { AS data-type |  LIKE field [ VALIDATE ] }
      [ field-options ]
  ] ...
  [ INDEX index-name
      [ IS [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ]
      { index-field [ ASCENDING | DESCENDING ] } ...
  ] ... 

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:

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.

SYNTAX
{
   [ BGCOLOR expression ]
   [ COLUMN-LABEL label ]
   [ DCOLOR expression ]
   [ DECIMALS n ]
   [ EXTENT n ]
   [ FONT expression ]
   [ FGCOLOR expression ]
   [ FORMAT string ] 
   [ HELP help-text ]
   [ INITIAL
       { constant | { [ constant [ , constant ] ... ] } }
   ]
   [ LABEL label [ , label ] ... ]
   [ MOUSE-POINTER expression ]
   [ [ NOT ] CASE-SENSITIVE ]
   [ PFCOLOR expression ]
   { [ view-as-phrase ] }
 } 

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:

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR
  INDEX x a DESC b DESC c DESC. 

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR
  INDEX x a DESC b c. 

The following two temp-table definitions are also equivalent:

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR
  INDEX x a ASC b DESC c DESC. 

DEFINE TEMP-TABLE foo FIELD a AS CHAR FIELD b AS CHAR FIELD c AS CHAR
  INDEX x a ASC b DESC c. 

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.

r-tmptb1.p
DEFINE TEMP-TABLE temp-item
   FIELD cat-page LIKE item.cat-page
   FIELD inventory LIKE item.price LABEL "Inventory Value"
   INDEX cat-page IS PRIMARY cat-page ASCENDING
   INDEX inventory-value inventory DESCENDING.
DEFINE VARIABLE cutoff LIKE item.price.
DEFINE VARIABLE inv-value LIKE item.price.
DEFINE VARIABLE report-type AS INTEGER INITIAL 1.
DEFINE BUTTON ok-butt LABEL "OK" AUTO-GO.
DEFINE BUTTON cancel-butt LABEL "CANCEL" AUTO-ENDKEY.
FORM
   cutoff LABEL "Inventory Lower Cutoff for each Catalog Page"
      AT ROW 1.25 COLUMN 2
   report-type LABEL "Report Sorted ..."
      AT ROW 2.25 COLUMN 2 
      VIEW-AS RADIO-SET RADIO-BUTTONS
         "By Catalog Page",   1,
         "By Inventory Value", 2
   SKIP ok-butt cancel-butt
   WITH FRAME select-frame SIDE-LABELS WIDTH 70
      TITLE "Specify Report ..." VIEW-AS DIALOG-BOX.
FOR EACH item BREAK BY item.cat-page:
   ACCUMULATE price * on-hand (SUB-TOTAL BY item.cat-page).
   IF LAST-OF(item.cat-page) THEN DO:
      inv-value = ACCUM SUB-TOTAL BY item.cat-page (price * on-hand).
      CREATE temp-item.
      temp-item.cat-page = item.cat-page.
      inventory = inv-value.
   END.
END. /* FOR EACH item */
ON CHOOSE OF ok-butt
  DO:
    HIDE FRAME select-frame.
    IF report-type = 1 THEN
       FOR EACH temp-item USE-INDEX cat-page WITH FRAME rpt1-frame:
          IF inventory >= cutoff THEN
             DISPLAY temp-item.cat-page inventory.
       END.
    ELSE
       FOR EACH temp-item USE-INDEX inventory-value WITH FRAME rpt2-frame:
          IF inventory >= cutoff THEN
             DISPLAY temp-item.cat-page inventory.
       END.
    VIEW FRAME select-frame.
  END.
ENABLE ALL WITH FRAME select-frame.
WAIT-FOR CHOOSE OF cancel-butt OR WINDOW-CLOSE OF CURRENT-WINDOW. 

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.

r-ttbfld.p
/* r-ttbfld.p */

/* Extracts a temp-table buffer-field attribute.
 * With RCODE-INFORMATION, mylabel is "xx" (the actual value).
 * Without RCODE-INFORMATION, mylabel is "f1" (the default value).
 *   With NO-ERROR, no warning message appears
 *   Without NO-ERROR, the warning message appears  
 */

DEFINE TEMP-TABLE x /* RCODE-INFORMATION */
  FIELD f1 AS CHARACTER LABEL "xx".
DEFINE VARIABLE h AS HANDLE.
h =    BUFFER x:HANDLE.
DEFINE VARIABLE fh AS HANDLE.
fh = h:BUFFER-FIELD(1).
DEFINE VARIABLE mylabel AS CHARACTER.
mylabel = fh:LABEL /* NO-ERROR */.
display mylabel. 

NOTES

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