When temporary tables are created, the table structure is created in memory and the data is stored in temporary scratch files. No structure or data is stored in the database files. If indexes and keys are built for a temporary table, the key data is also written to the temporary files.
Use the CREATE TABLE command to create a temporary table. Use the CREATE VIEW command to create a temporary view. The INTERSECT, JOIN, PROJECT, SUBTRACT, UNION, ATTACH, and SATTACH commands can also be used to create a temporary table.
The following command projects a temporary table which contains customer records:
PROJECT TEMPORARY tCustomer FROM Customer USING ALL
The following creates two temporary tables to load with invoice header and invoice detail records:
CREATE TEMPORARY TABLE tInvoiceHeader +
(TransID INTEGER, +
CustID INTEGER, +
EmpID INTEGER, +
TransDate DATE, +
BillToCompany TEXT (40), +
BillToAddress TEXT (30), +
BillToCity TEXT (20), +
BillToState TEXT (2), +
BillToZip TEXT (10), +
ShipToCompany TEXT (40), +
ShipToAddress TEXT (30), +
ShipToCity TEXT (20), +
ShipToState TEXT (2), +
ShipToZip TEXT (10), +
NetAmount CURRENCY, +
Freight = (netamount* .01) CURRENCY, +
Tax = (netamount* .081) CURRENCY, +
InvoiceTotal= (NetAmount+Freight+Tax) CURRENCY)
COMMENT ON TABLE tInvoiceHeader IS 'Invoice Header Information'
CREATE TEMPORARY TABLE tInvoiceDetail +
(TransID INTEGER, +
DetailNum INTEGER, +
Model TEXT (6), +
Units INTEGER, +
Price CURRENCY, +
Discount REAL, +
SalePrice= (Price-(Price*Discount/100)) CURRENCY, +
ExtPrice= (Units* SalePrice) CURRENCY)
AUTONUM DetailNum IN tInvoiceDetail USING 1,1
COMMENT ON TABLE tInvoiceDetail IS 'Invoice Header Information'
The following creates a temporary view of year-to-date totals:
CREATE TEMPORARY VIEW tYTDInvoiceTotal +
(CustID, YTDInvoiceTotal) AS +
SELECT CustID, (SUM(InvoiceTotal)) FROM InvoiceHeader +
GROUP BY CustID
COMMENT ON VIEW tYTDInvoiceTotal IS +
'Year-To-Date Invoice Total by Customer'
