Suprtool Tip: Update From a Table

Suprtool: Extract from a Table

Suprtool added the Update-From-Table feature in version 4.4.10. This article describes the power of this new feature and is followed by another article showing a practical application for Update-From-Table.

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, 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)
>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

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

Sync-up Your Master Totals Via Update-From-Table

Many applications keep a running total in the master record of all the detail transactions. You can use the new Update-From-Table feature in Suprtool to easily do this.

The following example reads the D-SALES detail set and summarizes the total sales for each customer.

 >base      store,1,writer
 >get       d-sales
 >sort      cust-account
 >duplicate none keys total sales-total
 >extract   cust-account
 >output    summary,link
 >xeq

 >input summary;list standard;x
 Aug 23, 2001 11:56

 CUST-ACCO ST-TOTAL-1

      123                       136193+
     4566                       723309+
 IN=2, OUT=2. CPU-Sec=1. Wall-Sec=1.

Now let's update the master dataset records. Notice the new syntax on the TABLE and the EXTRACT commands.

>table t-sales,cust-account,file,summary,data(st-total-1)
>get   m-customer
>if    $lookup(t-sales,cust-account)
>update
>extract sales-history = &
  $lookup(t-sales,cust-account,st-total-1)
>xeq

Finally, here is what the Master set records look like:

 >g m-customer;ext cust-account,sales-history;l;x

 >GET M-CUSTOMER (134) >OUT $NULL (0)
 CUST-ACCOUNT    = 4566           SALES-HISTORY   = 723309

 >GET M-CUSTOMER (187) >OUT $NULL (1)
 CUST-ACCOUNT    = 123            SALES-HISTORY   = 136193

Update From a Table Via Command Generation

Note: this article was written before Suprtool had the Update from a Table feature. It contains a clever way to do mass updates by generating a Suprtool task for each update value. Perhaps it will give you ideas for solving other problems.

One common database maintenance task is a simple mass update: read a data file with two fields, where the first field is a key value, and the second field is a new value for a particular field in the record.

Suprtool does not currently have a command for this operation. However, there is another way to do it: transform the data file into a Suprtool script and then execute the script.

In this example, the DATAFILE is a self-describing file with two fields called KEYVALUE which is a product-id, and NEWVALUE which is the new product description to update. You would create this file from some other data source, using the Output,Link Command in Suprtool to make it self-describing (i.e., it has an internal data dictionary defining the fields). Our DATABASE has a DATASET containing a PRODUCT-ID and a PRODUCT-DESC. For each PRODUCT-ID value in our DATAFILE (the KEYVALUEs), we want to replace the PRODUCT-DESC in the DATABASE with the corresponding NEWVALUE from the DATAFILE.

For each record in the DATAFILE, the CMDFILE script that is generated contains four Suprtool commands: Chain to retrieve a record by keyvalue, Update to update the selected record, Extract to replace the value of the second field with the new value, and Xeq to perform the task. Of course, you would modify the bold names in this example to match your file, dataset, and field names.

base DATABASE
in  DATAFILE
ext 'chain DATASET, PRODUCT-ID="', KEYVALUE , '"'
ext ';update ciupdate'
ext ';extract PRODUCT-DESC="', NEWVALUE, '"'
ext ';xeq'
out CMDFILE
xeq
use CMDFILE