Using Tables to Replace Quiz Links

To select the intersection of two files, standard reporting programs rely on linking the two files together. For example, to report sales generated by California customers, your Quiz code would look like this:

   > access m-customer link to d-sales
   > select m-customer if state-code = "CA"
   > report product-no sales-qty ....
   > ....etc

The customer file is accessed only for purposes of qualifying which sales records should be selected. Quiz retrieves the D-Sales records via keyed reads for each Cust-Account value, so D-Sales records are grouped by Cust-Account, though not necessarily in Cust-Account sequence. If the report should be in Cust-Account sequence, you need to add a sort to the above commands.

This could be done more efficiently by using Suprtool's Table command. First, create a file of qualified customers:

   > base    store,5,WRITER
   > get     m-customer
   > if      state-code = "CA"
   > sort    cust-account
   > extract cust-account
   > output  calcust,temp,link
   > xeq

Then use this file to load a table, which is used to select the required D-Sales records:

   > get     d-sales
   > table   custable,cust-account,sorted,calcust
   > if      $lookup(custable,cust-account)
   > extract product-no, sales-qty ......
   > list    standard .....
   > xeq

Suprtool reads the D-Sales dataset serially, with fast MR/NOBUF access (and thus minimum disk I/O), to return the qualified records.

An even more dramatic improvement can be achieved in cases where files are being linked in order to test for missing records. For example, to select which customers do not have sales records, the following Quiz code would apply:

   > access m-customer link to d-sales optional
   > select if not record d-sales exists
   > ......

This would perform keyed reads into D-Sales for every M-Customer record, and select only when no sales records are found. Again, this could be achieved more efficiently with Suprtool:

   > base       store,5,WRITER
   > get        d-sales
   > sort       cust-account
   > duplicate  none keys
   > extract    cust-account
   > output     salfile,temp,link
   > xeq
   > get        m-customer
   > table      saltable,cust-account,sorted,salfile
   > if         not $lookup(saltable,cust-account)
   > list .....  etc

This method involves two passes, but because of Suprtool's efficient reads, it is generally much faster than the traditional approach. [Hans Hendriks]

....Back to the Suprtool Q&A Page