PreviousNextIndex

DEFINE TEMP-TABLE statement

Defines a temp-table that is created at compile time. The AVM stores temp-tables in memory (with potential overflow to disk). Among procedures, a temp-table can be either global (lasting for the entire ABL 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 non-shared (visible just to the procedure that created it). In a class, a temp-table can be defined for use within a single class or class hierarchy.

Syntax 

DEFINE { [ [ NEW [ GLOBAL ] ] SHARED ] |  
            [ PRIVATE | PROTECTED ] [ STATIC ] } 
  TEMP-TABLE temp-table-name [ NO-UNDO ]  
  [ NAMESPACE-URI namespace ] [ NAMESPACE-PREFIX prefix ] 
  [ XML-NODE-NAME node-name ] [ SERIALIZE-NAME serialize-name ]  
  [ REFERENCE-ONLY ] 
  [ LIKE table-name 
      [ VALIDATE ] 
      [ USE-INDEX index-name [ AS PRIMARY ] ] ... ] 
  [ LIKE-SEQUENTIAL table-name 
      [ VALIDATE ] 
      [ USE-INDEX index-name [ AS PRIMARY ] ] ... ] 
  [ RCODE-INFORMATION ]  
  [ BEFORE-TABLE before-table-name ]  
  [ FIELD field-name 
      { AS data-type |  LIKE field [ VALIDATE ] } 
  [ field-options ] 
  ] ... 
  [ INDEX index-name 
      [ [ AS | IS ] [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ] 
      { index-field [ ASCENDING | DESCENDING ] } ... 
  ] ...

NEW SHARED TEMP-TABLE temp-table-name
NEW GLOBAL SHARED TEMP-TABLE temp-table-name
SHARED TEMP-TABLE temp-table-name
[ PRIVATE | PROTECTED ] [ STATIC ] TEMP-TABLE temp-table-name
TEMP-TABLE temp-table-name
NO-UNDO
NAMESPACE-URI namespace
NAMESPACE-PREFIX prefix
XML-NODE-NAME node-name
SERIALIZE-NAME serialize-name
REFERENCE-ONLY
LIKE table-name [ USE-INDEX index-name  [ AS PRIMARY ] ] . . .
LIKE-SEQUENTIAL table-name [ USE-INDEX index-name  [ AS PRIMARY ] ] . . .
VALIDATE
RCODE-INFORMATION
BEFORE-TABLE before-table-name
FIELD field-name
AS data-type
LIKE field
field-options
HELP help-text
SERIALIZE-HIDDEN
SERIALIZE-NAME serialize-name
TTCODEPAGE | COLUMN-CODEPAGE codepage
XML-DATA-TYPE string
XML-NODE-TYPE string
XML-NODE-NAME node-name
INDEX index-name [ [ AS | IS ] [ UNIQUE ] [ PRIMARY ] [ WORD-INDEX ] ]
index-field [ ASCENDING | DESCENDING ]
Examples

The following procedure creates a temp-table (temp-item) that stores the total inventory value (Item.Price * Item.OnHand) for each catalog page (Item.CatPage) in the sports2000 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 temp-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.CatPage 
  FIELD inventory LIKE Item.Price LABEL "Inventory Value" 
  INDEX cat-page  IS PRIMARY cat-page ASCENDING 
  INDEX inventory-value inventory DESCENDING. 
DEFINE VARIABLE cutoff      NO-UNDO LIKE item.price. 
DEFINE VARIABLE inv-value   NO-UNDO LIKE item.price. 
DEFINE VARIABLE report-type AS INTEGER NO-UNDO 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.CatPage: 
  ACCUMULATE Item.Price * Item.OnHand (SUB-TOTAL BY Item.CatPage). 
  IF LAST-OF(Item.CatPage) THEN DO: 
    inv-value = ACCUM SUB-TOTAL BY Item.CatPage (Item.Price * Item.OnHand). 
    CREATE temp-item. 
    temp-item.cat-page = Item.CatPage. 
    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.

For examples of instance and static temp-table data member definitions, see the descriptions of r-CustObj.cls, r-CustObjStatic.cls, and r-CustObjAbstract.cls in the CLASS statement reference entry.

Notes
See also

Class-based data member access, CREATE-LIKE( ) method, CREATE-LIKE-SEQUENTIAL( ) method, CREATE TEMP-TABLE statement, DEFINE DATASET statement, DEFINE WORK-TABLE statement, NUM-REFERENCES attribute, RUN statement

OpenEdge Release 10.2B
Copyright © 2009 Progress Software Corporation
PreviousNextIndex