Get the most out of your Robelle
Extract from a Table
By Neil Armstrong
created as a high-performance extract and sort utility, but its users
wanted to utilize it for database update tasks as well. So we added
the Update command, but the only thing you could change during the
pass was to set a field to a constant (i.e., reset ytd totals, etc.).
Interesting, but very limited.
For some time, users have
been aggressively asking us how to update a field in a dataset, or
even a file, with a value from another file, based on a key. A
classic example: Your boss comes to you with a list of new prices for
certain parts and asks you to update the Part-Master
Big News: You can now do
this easily in Suprtool. Starting with pre-release 4.4.10,
just load the new prices into a Table, index by the product number
(prodno), then Extract the price field from each record and replace
it with a $lookup on the table. Here is the code:
>extract price =
We do the If
$lookup to select only the parts which have new prices, then do
Extract with $lookup to replace the existing price with a new one.
The Update command forces a database update on each selected record
and must come before the Extract command.
As you can see, Suprtool now has
the ability to load and extract data to and from a Table. Lets
go through the capability in more detail.
For years Suprtool had the
combination of the Table command and associated $lookup function to
find the key values that have been loaded into a table, but only when
doing record selection.
Here is a simple example of
record selection using $lookup, where we build a file of orders that
both have a status of OP and are in a list that comes
from the file orders;
= OP and $lookup(mytable,order-no)
lets see how a Table can be used to add additional useful
information to a record. Lets say we build this table of
Canadian provinces (The file prov-file is assumed to be a Link, or
self-describing, file, created by a previous pass of Suprtool.)
At this point the key into
the Table is the prov-code item and for each entry in the Table there
is one associated prov-name.
To append prov-name to each output
record, we read the customer dataset, extracting the customer name.
We also Define prov-name as a new field and extract it for the output
record, but we fill it with a value that is based on the prov-code
for each customer entry:
To update a
dataset, you do the same commands, but you insert an Update command
prior to the Extract from a Table. Below is an example that shows how
to update an IMAGE record using data values from a Table.
Lets assume that we have new
unit cost information for each product:
NEWCOSTS.NEIL.GREEN (SD Version B.00.00)
Limit: 13 EOF: 13
Entry Length: 12 Blocking: 64
We load a
table with the product number key value (prod-no) and the new unit
cost data value (unit-cost):
We can then
select that unit-cost field from the prodcost-table using the Extract
>extract unit-cost =
Here is the entire task, keeping in
mind that Update must be specified before the Extract command:
Database password [;]?
unit-cost = $lookup(prodcost-table,prodno,unit-cost)
currently doing a performance pass on the code for this enhancement,
making the whole process faster, and also trying to speed up all If
$lookup operations in Suprtool.
This version of Suprtool is
currently in beta-test. Robelle is looking for keen users to do some
testing of the pre-release. Interested Suprtool sites, please send
email to firstname.lastname@example.org
or telephone 604.592.1700.
This is the most requested
Suprtool enhancement request ever in the Suprtools 20 years.
Copyright The 3000 NewsWire. All