July 2001

Get the most out of your Robelle Tools

Suprtool: New Extract from a Table

By Neil Armstrong

Suprtool was 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 dataset.

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:

>table newprices,prodno,file,bosslist,data(price)
>get part-master
>if $lookup(newprices,prodno)
>update
>extract price = $lookup(newprices,prodno,price)
>output auditcp
>xeq

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. Let’s 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;”

>base mydb
>get order-details
>table mytable,order-no,file,orders
>if order-status = “OP” and $lookup(mytable,order-no)
>output qualords,link
>xeq

Now let’s see how a Table can be used to add additional useful information to a record. Let’s 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.)

>table province-table,prov-code,file,provfile,data(prov-name)

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:

>get customers
>ext cust-name
>def full-prov-name,1,30
>ext full-prov-name=$lookup(province-table,cust-prov-code,prov-name)
>out somefile
>xeq

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.

Let’s assume that we have new unit cost information for each product:

>form newcosts
File: NEWCOSTS.NEIL.GREEN (SD Version B.00.00)
Entry: Offset
PRODNO Z8 1
UNIT-COST P8 9
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):

>table prodcost-table,prodno,file,newcosts,data(unit-cost)
We can then select that unit-cost field from the prodcost-table using the Extract command:

>extract unit-cost = $lookup(prodcost-table,prodno,unit-cost)

Here is the entire task, keeping in mind that Update must be specified before the Extract command:

>base store.suprtpis
Database password [;]?
>get d-inventory
>table prodcost-table,prodno,file,newcosts,data(unit-cost)
>update
>if $lookup(prodcost-table,prodno)
>extract unit-cost = $lookup(prodcost-table,prodno,unit-cost)
>xeq

We are 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 support@robelle.com or telephone 604.592.1700.

This is the most requested Suprtool enhancement request ever in the Suprtool’s 20 years.

neil.armstrong@robelle.com