Robelle Tech: Unbelievable Suprtool Enhancements This Year

June 2003

Get the most out of your Robelle Tools

Unbelievable Suprtool Enhancements This Year

Neil Armstrong, the software architect in charge of Suprtool, has had an incredible year of programming productivity. As can be seen from Suprtool release 4.7. In spite of HP’s dumping the HP 3000, Neil has not lost his enthusiasm. He has added more new functions to Suprtool than in any previous year. And those new functions work both with TurboIMAGE/MPE and with Eloquence/HP-UX and Oracle/HP-UX.

Here is a quick summary of what is new:

The $Number function reads a free-form ASCII number with signs, decimal places and currency. The $Total function keeps a running total for a numeric field. The $SubTotal function totals numeric fields on level-breaks. The $Split function breaks strings into multiple fields,

The Join command in Suprlink does many-to-many links (this was actually coded by Bob Green, but Neil did fix a nasty bug in it.) The Clean option prepares data for export, so that it will import without fail into Excel, Oracle, etc. The Escape command exports problem characters with an “escape character.” The $Counter function increments a field for every record selected.

Suprtool, STExport and Suprlink now support environment variables on HP-UX, and Suprtool/UX supports Eloquence 7.0 features and expanded limits.

In earlier Robelle Tech columns we described $Number, many-to-many links, Clean and $Counter. These application notes can be found at www.robelle.com/tips/suprtool.html

In this column we will explain the $Subtotal and $Split functions, and give you a bonus, some Suprtool coding suggestions.

$Subtotal: Control Break Totals

Suprtool now has the ability to keep a running subtotal for any numeric field based on a given sort key. A sample use of the $subtotal function could be:

>def mytotal,1,14,packed
>get orders
>sort order-number
>ext order-number
>ext part-number
>ext description
>ext sales-amount
>ext mytotal = $subtotal(sales-amount,order-number)
>out sales,link
>xeq

This would result in a file containing a running subtotal in the field mytotal for a given order-number. (The target data must be a packed field with 28 digits, in order to help avoid overflow issues.)

You could then generate a simple report with the simple Suprtool commands:

>in sales
>list standard
>xeq

The basic syntax for the $subtotal function in the Extract command is:

extract targetfield = $subtotal(field,sort-field)

You must specify the sort command before referencing the sort-field in the $subtotal function. You can subtotal up to ten fields per pass.

Splitting Byte Fields

Suprtool can now extract portions of a byte field based on the occurrence of certain characters. Considering the following data:

Armstrong/ Neil/ Patrick
Green/ Bob/ Miller
Fritshaw/ Elizabeth/
Edwards/ Janine/
Armstrong/Arthur/Derek

The $split function can extract each token into separate fields. The syntax for the $split function is:

$split(Field,Start Character,Occurrence,

End Character,Occurrence)

The following task will $split the data in the whole field into three separate fields, left justified.

>in namefile
>define lastname,1,30
>define firstname,1,20
>define middlename,1,20
>extract lastname = $split(wholename,first,”/”)
>extract firstname=$trim($split(wholename,”/”,”/”))
>extract middlename=$trim($split(wholename,”/”,2,” “,2))
>out names,link
>xeq

The first extract statement tells Suprtool to extract the bytes from the field wholename, starting at the beginning (first keyword), and stopping at the “/” character.

The second extract statement tells Suprtool to extract the bytes between the first occurrence of the “/” character to the next occurrence of the “/” character, and then that string is trimmed of spaces as it is nested within the $trim function.

The third and final extract statement tells Suprtool to extract the bytes beginning with the second occurrence of the “/” character to the second occurrence of the space character.

If the target field is not long enough to hold the data Suprtool will abort with an error. You can easily prevent this from happening on blank fields by nesting the $split statement within a $trim or $rtrim function.

Suprtool Coding Tips

Neil was so excited by this year’s improvements to Suprtool that he set aside time to document his ideas on Suprtool programming style.

Suprtool is not only a database tool, but is also a language. Like any “programming” language there are things you can do to improve the readability of the scripts you are writing.

Suprtool does very little work when accepting commands. It does syntactic checking and some semantic checking of the commands, but Suprtool does very little work until you “xeq” the task or exit. (The Table command is the exception to this rule as it may have to read and sort a file to fill the table!)

Here is some sample bad code (although it is syntactically correct, the ordering of the commands does not make the logic clear):

base mytest,5
table mytable,order-number,file,myords
get mydataset
item order-date,date,ccyymmdd
output myfile,link
sort order-number
ext order-number
if order-date=$today(-1) and
$lookup(mytable,order-number)
sort order-date
ext order-quantity,order-ctgy
ext order-description
ext order-dollars
ext order-tax
xeq
exit

Writing good Suprtool code can be achieved by simply dividing up commands into some simple categories. These are: Source of records, Selection of records, Order of records, Fields for output, Optional steps, and Destination of records

In the Suprtool course there is one slide (shown at left) that I always tell students to mark as the most important slide in the course, as it clearly outlines the commands and what they are for.

So applying this slide to the previous code we would get the following:

base mytest,5
get mydataset

table mytable,order-number,file,myords
item order-date,date,ccyymmdd
if order-date=$today(-1) and
$lookup(mytable,order-number)

sort order-number
sort order-date

ext order-number
ext order-quantity
ext order-ctgy
ext order-description
ext order-dollars
ext order-tax

output myfile,link
exit

We moved the code around to be separated into logical groupings, making the code a lot easier to read and maintain. The Table command is near the If command for easy reference. The Item command is before the If command and is necessary to tell the If command the date type that the order-date is, so I keep it near the If command. We have a clear definition of the input source.

The two sort keys are clearly defined as well as each field.

If you needed to define a field to rename a field being extracted, say order-dollars to be order-total, I would change the script as follows:

base mytest,5
get mydataset

def order-total,1,4,double

table mytable,order-number,file,myords
item order-date,date,ccyymmdd
if order-date=$today(-1) and
$lookup(mytable,order-number)

sort order-number
sort order-date

ext order-number
ext order-quantity
ext order-ctgy
ext order-description
ext order-total=order-dollars
ext order-tax

output myfile,link
exit

The Define commands can be grouped where you specify the input source, or even as I have them, as their own separate section. Next time you are developing some Suprtool scripts, why not give this guideline a try? We trust you will find your code much more readable.