The general syntax is:
>extract field-name = expression
The "field-name" can be any field from a database, a self-describing file, or a Define command. An "expression" is any numeric value, or field name, or an arithmetic combination of the two. For example,
>define DoubleField,1,4,double
>extract DoubleField = PackedField + 10
If you want to update a field with the value of another field in the same dataset, use the Update command before the Extract command.
>update {MPE only}
>extract TotalCost = UnitCost * SalesQty
This will also be very handy for Suprlink users when two files store the key field in different formats. For example, a Z6 display field to store CustomerNumber in one file, and a J2 integer field to store CustNo in another file.
This feature will be part of Suprtool 3.8 which is expected around April of 1996. If you would like to try a pre-release version, feel free to give us a call.
Progress messages are not displayed when the output is directed to $STDLIST, or when a task reads less than 50,000 records (minimum default). You can change the default number either temporarily in every task, or permanently by simply adding the following line to a file called Suprmgr.Pub.Sys on MPE or /usr/robelle/suprmgr on HP-UX.
set progress minimum 0
The IF command can now support arithmetic operations between fields, even fields of different numeric data types. This is useful in checking some of the 'business rules' that most applications have. These rules are usually included in the data entry programs but sometimes 'stuff happens' and the data gets messed up. Here is a simple task that uses this new feature to isolate which records are breaking the rule of "Invoice amount = price times quantity".
>base sales.db,5,reader {MPE, on HP-UX use >open ..}
>get d-invoices {MPE, on HP-UX use >select ..}
>if price * quantity < > amount
>list standard title "Price * Qty not = Amount" device LP
>xeq
This idea can be used in a monthend job that prints exception reports. [Taken from the "Ensuring Data Integrity Using Suprtool" tutorial given at Interex in Toronto.]
Financial transactions are often processed in batches. Between jobs aborting, program bugs, and less-than-careful data fixes by MIS staff, these batches can get out-of-balance. Using the Total command can quickly verify that all transactions net to zero.
>base fms.gl,5,reader {MPE, on HP-UX use >open ..}
>get d-transactions {MPE, on HP-UX use >select ..}
>output $null
>total trans-amt
>xeq
Totals (SUN, JAN 29, 1995, 2:56 PM):
TRANS-AMT 81451+
IN=6, OUT=6. CPU-Sec=1. Wall-Sec=1.
By default the results of the Total command are displayed on the screen ($STDLIST), but can be appended to the output file. To do, this you need to turn Set Squeeze Off and add a new Total command as follows:
total $file filename,append {MPE only}
Up to 15 fields can be totalled in each pass.
[Taken from the "Ensuring Data Integrity Using Suprtool" tutorial given at Interex in Toronto.]
A simple to way to add the record count to the output file in MPE/iX is to use the Echo command and the SuprtoolFullCount hpvariable. The only thing to remember is that the output file must have room for the extra line.
!job mgr.test
!run suprtool.pub.robelle
base store
get m-customer
numrecs 105% {build output bigger}
set squeeze off {don't make limit = eof}
output cust
exit
!
!file cust,old
!echo Total number of customers = !suprtoolfullcount>> *cust
!eoj
A Suprtool command line cannot exceed 256 characters. You can reach this limit if you try to stack all your task commands into a single entity.
input ...; define ...; &
extract ...; duplicate ...; if ...; &
sort ...; output ...;xeq
The only solution in this situation is to put individual commands on separate lines. Then you can control the length of each command. If you are not stacking commands, you may also run into this problem if you use a long and complex If command. In that case, there are different ways to get around the problem.
If your command is complex and you are using long item names, you can assign shorter names by using Define statements. Then you can refer to the same item by using the short or long form.
define low,ReOrderQuantity
define curqty,OnHandQuantity
At the expense of readability, you can also remove unnecessary spaces. For example, you can use
if curqty>low
instead of
if on-hand-qty > re-order-quantity.
If your If command is too long because your list contains numerous values, you can load these values into a table.
table custtbl,custno,item,"111111"
table custtbl,custno,item,"222222"
table custtbl,custno,item,"333333"
if $lookup(custtbl,custno)
This table can replace the following If command,
if custno="111111","222222","333333"
If you cannot apply these suggestions to your situation, you can also use the $READ function. The maximum length of the If command is then based on the complexity of the expression and not its length.
The $READ function reads the If expression from $STDINX, or from the use-file when it contains the If command. $READ continues to prompt for input lines until you press Return or enter "//". You must remember to enter all the necessary parts of the If expression including connectors like "and", "or" and commas. With $READ, you do not need an ampersand (&) to continue from one line to the next.
>in supplier {self-describing file}
>if $read {prompt for the expression}
-CustStatus = "20" and {$READ prompts with "-"}
-State = "AZ", {the comma is still needed}
- "OR" {no comma on the last line}
- {blank line to terminate $READ}