I hope that all people interested in using temp tables can use this advise
----- Original Message -----
From: "A. Razzak Memon" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Thursday, May 05, 2011 2:44 PM
Subject: [RBASE-L] - TEMPORARY tables and views (was Re: Over
reloading/repacking)
At 03:26 AM 5/5/2011, Marco Groeneveld wrote:
Just a question wh was it not working with the temp tables. Was
this also with the latest release 9.1?
Maybe if something is changed/updated in this solution you don't
need the tables en so don't need to repair th database
Marco,
One should always DISCONNECT and then CONNECT the database before
using the RELOAD or UNLOAD commands.
FYI, TEMPORARY tables and views are non-permanent tables/views that
only exist for the duration of a database session. When a database
session terminates, its temporary tables/views are automatically
DROPped.
TEMPORARY tables/views are only visible to the R:BASE session that
creates them and remain invisible to other R:BASE users. Several
users can create temporary tables/views with the same name, and
each user will see only that particular version of the table/view.
TEMPORARY tables are ideal for holding short-term data used by the
current R:BASE session. For example, suppose you need to do many
SELECTs on the result of a complex query. An efficient strategy is
to execute the complex query once, then store the result in a
temporary table.
You can also create an INDEX on the temporary table to speed up
queries with the CREATE INDEX command. In addition to indexes, you
can create rules, constraints and triggers on temporary tables.
You may CREATE or turn a permanent table into a TEMPORARY table
using the enhanced Data Designer.
You may create a TEMPORARY table/view using CREATE TEMPORARY
TABLE tablename ... or CREATE TEMPORARY VIEW viewname commands.
-- Example 01 (Creating Temporary Table)
SET ERROR MESSAGE 2038 OFF
DROP TABLE tInvoiceHeader
SET ERROR MESSAGE 2038 ON
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'
RETURN
-- Example 02 (Creating Temporary View)
SET ERROR MESSAGE 677 OFF
DROP VIEW YTDInvoiceTotal
SET ERROR MESSAGE 677 ON
CREATE TEMPORARY VIEW `YTDInvoiceTotal` +
(CustID,Company,YTDNetAmount,YTDFreight,YTDTax,YTDInvoiceTotal) +
AS SELECT +
CustID,BillToCompany,(SUM(NetAmount)),(SUM(Freight)),(SUM(Tax)), +
(SUM(InvoiceTotal)) +
FROM InvoiceHeader GROUP BY CustID,BillToCompany
COMMENT ON VIEW `YTDInvoiceTotal` IS 'Year-To-Date Invoice Totals by
Customer'
RETURN
You may also create a TEMPORARY table using the PROJECT TEMPORARY
tablename FROM tableviewname USING collist WHERE ... ORDER BY ...
command.
You may define all required TEMPORARY tables/views for forms as
"On Before Design Action" before designing a form and "On Before
Start" EEP before using (EDIT USING, ENTER USING, BROWSE USING)
the form.
You may define all required TEMPORARY tables/views for Reports as
"On Before Design ..." action before designing a report/sub-report
and "On Before Generate ..." action.
You may define all required TEMPORARY tables/views for Labels as
"On Before Design ..." action before designing a label and "On
Before Generate ..." action.
Last but not least, you may also define all TEMPORARY tables and
views before printing online dynamic labels/reports using R:Web
Reports 9.1 (32/64).
Very Best R:egards,
Razzak.
