 |
 |
 |
|
|
February 2005
Get the most
out of your Robelle Tools
Two Keys for a
Suprtool Table
By Neil Armstrong,
Robelle
Recently I
received the following request on how to do something using Suprtool.
The issue was that the customer wanted to do a table lookup and
subsequent Update from the table data, however, the record or table
key needed to match on two values, one that was a double integer and
the other was a single integer(well actually a logical, but the same
number of bits). The solution, once you wrap your head around it, is
amazingly simple.
The Problem
I want to take the value of a field in an old database and put it into the same field for the same record in another database.
The key path
is ACCOUNT. However it needs to also compare on the non-key field
SUFFIX. If ACCOUNT and SUFFIX match then the YTD-INT field I
extracted from the OLDDB:LOAN-FILE needs to be put into the YTD-INT
field of the NEWDB:LOAN-FILE.
I can
make the link file by:
ba olddb
get
loan-file
ext account
ext suffix
ext ytd-int
sort
account
sort suffix
output loanfile link
xeq
However, how do I get this field into the corresponding
record in the “newdb:loan-file” dataset?
The
one thing I wasn’t sure of with the $LOOKUP is that the
comparison needs to be on both the ACCOUNT field and the SUFFIX
field. If they match between the two sets, then the YTD-INT field
from the OLDDB can replace the value in that field of the NEWDB. Here
is the form for the loan-file:
LOAN-FILE Detail Set# 13
Entry: Offset
ACCOUNT I2 1 (!ACCOUNT-FILE(SUFFIX))
SUFFIX K1 5
NOTE X8 7
.
.
.
YTD-INT I2 121
FILLER05 5I1 1221
UPDATE-COUNTER I1 1231
Solution
The key to the sample solution below is just defining a single field that is equivalent to the number and location of the bytes that are in the two values that must match — which for clarity purposes are the same in my samples below.
Your two key values are:
ACCOUNT I2
equivalent to 4 bytes
SUFFIX K1
equivalent to 2 bytes
This makes
for a total of six bytes. My example below is essentially the
same:
>form lineitems
Database: sample
TPI: Eloquence B.07.00 B.07.00.21
LINEITEMS Detail Set# 5
Entry: Offset
ORDERID I2 1 (!ID)
ORDERLINE I1 5
ITEMNO X16 7 (PARTS)
QTY E4 23
PRICE E4 31
ITEMCOUNT X2 39
DELIVDATE I2 41
So the first step
is to prepare the data that you want to update the new database with,
which in the example given was for account, suffix and then the data
value that you wanted to update which is ytd-int. In my case the key
fields are orderid, orderline and delivdate for the update value.
:comment
:comment
Extract from old database
:comment
>set fastread on
>base sample
>get lineitems
>def
mykey,1,6,byte {define two fields as one as byte type}
>ext mykey
>ext
delivdate=20051213 {just picked an arbitrary date}
>sort mykey
>out
fortable,link
>xeq
IN=136,
OUT=136. CPU-Sec=1. Wall-Sec=1.
You will not
be able to read the integer data in the byte field, however, since it
is all just bits and bytes the correct things will happen when
loading and doing the $table lookup. This is what the form command
will look like and some of the data:
>in fortable
>form
File: fortable (SD Version B.00.00) No linefeeds
Entry: Offset
MYKEY X6 1 <>
DELIVDATE I2 7
Entry Length: 10 Blocking: 1
>num 5
>list
>xeq
>IN fortable (0) >OUT $NULL (0)
MYKEY = ... DELIVDATE = 20051213
>IN fortable (1) >OUT $NULL (1)
MYKEY = ... DELIVDATE = 20051213
>IN fortable (2) >OUT $NULL (2)
MYKEY = ... DELIVDATE = 20051213
>IN fortable (3) >OUT $NULL (3)
MYKEY = ... DELIVDATE = 20051213
>IN fortable (4) >OUT $NULL (4)
MYKEY = ... DELIVDATE = 20051213
Warning: NUMRECS exceeded; some records not processed.
IN=6, OUT=5. CPU-Sec=1. Wall-Sec=1.
Now the next step
involves loading the table with the data that you want to update in
the new database:>base sample
Database password [;]?
>get lineitems
>def mykey,1,6
>table mytable,mykey,file,fortable,data(delivdate)
There are 136 entries in MYTABLE
>if $lookup(mytable,mykey)
>update
>ext delivdate=$lookup(mytable,mykey,delivdate)
>xeq
IN=136, OUT=136. CPU-Sec=1. Wall-Sec=1.
So to translate my
example to your environment the technique is essentially the same,
just the difference in file and field names:
:comment
:comment get
data from olddb
:comment
base olddb
get loan-file
def mykey,1,6
ext mykey
ext ytd-int
sort mykey
out fortable,link
xeq
:comment
:comment
update the new
:comment
base newdb
get loan-file
table
mytable,mykey,file,fortable,data(ytd-int)
if
$lookup(mytable,mykey)
update
ext
ytd-int=$lookup(mytable,mykey,ytd-int)
xeq
You can add
selection criteria to suit, depending on whether or not you need to
exclude records from the old database or exclude records that would
be updated.
Now for another
Suprtool tip….
Prompting
from a Job
We often get asked how to prompt for input from a jobstream. Now our first suggestion is to see if they have Streamx from Vesoft which is a portion of the Security/3000 product.
If the customer does not have this product, then the other option is to create a command file that uses MPE commands and IO re-direction to make a file that can be streamed. The first step is to prompt the user for the selection criteria,in this case the Catalog Code, which gets stored into the variable CATCODE.
The next steps are used to remove any files in the users logon called CATINVT, both permanent and/or temporary. The IO redirection to $NULL is used to hide any error messages that would print if the CATINVT file did not exist, which is a perfectly valid situation.
The rest of the command file is to echo out commands to a file which is then subsequently streamed.
INPUT CATCODE;PROMPT="ENTER THE CATALOG CODE:" PURGE CATINVT > $NULL PURGE CATINVT,TEMP > $NULL ECHO !!JOB CATINVT,JOBS.SGAII >> CATINVT ECHO !!PURGE CATINVTF >> CATINVT ECHO !!SUPR >> CATINVT ECHO BA MACITM,5,READALL >> CATINVT ECHO GET OFFER-ITEMS >> CATINVT ECHO DEF CATALOG,OFFER-ITEM[1],8 >> CATINVT ECHO DEF TEXTEDPNO,OFFER-ITEM[9],8,DISPLAY >> CATINVT ECHO DEF EDPNO,1,4,DOUBLE >> CATINVT ECHO IF CATALOG='!CATCODE' >> CATINVT ECHO EXT EDPNO=TEXTEDPNO >> CATINVT ECHO EXT PAGE-NO >> CATINVT ECHO OUT CATINV01,LINK,TEMP >> CATINVT ECHO X >> CATINVT ECHO IN CATINV01 >> CATINVT ECHO EXT EDPNO,PAGE-NO >> CATINVT ECHO SORT EDPNO >> CATINVT ECHO OUTPUT=INPUT >> CATINVT ECHO X >> CATINVT ECHO TABLE TAB1,EDPNO,SORTED,CATINV01 >> CATINVT ECHO GET ITEM-MAST >> CATINVT ECHO ITEM EXPECTED-DATE,DATE,CCYYMMDD >> CATINVT ECHO IF $LOOKUP(TAB1,EDP-NO) >> CATINVT ECHO EXT EDP-NO,ITEM-NO,DESCRIPTION,CURRENT-BO,& >> CATINVT ECHO AVAILABLE-INV,STATUS,PO-NUMBERS,& >> CATINVT ECHO EXPECTED-DATE,NEXT-QTY >> CATINVT ECHO SORT EDP-NO >> CATINVT ECHO OUT CATINV02,LINK,TEMP >> CATINVT ECHO X >> CATINVT ECHO LINK IN CATINV02 BY EDP-NO >> CATINVT ECHO LINK LINK CATINV01 BY EDPNO >> CATINVT ECHO LINK OUT CATINV03,TEMP >> CATINVT ECHO LINK EXIT >> CATINVT ECHO IN CATINV03 >> CATINVT ECHO EXT ITEM-NO,DESCRIPTION,AVAILABLE-INV,& >> CATINVT ECHO CURRENT-BO,STATUS,PAGE-NO,PO-NUMBERS,& >> CATINVT ECHO EXPECTED-DATE,NEXT-QTY >> CATINVT ECHO SORT PAGE-NO >> CATINVT ECHO SORT ITEM-NO >> CATINVT ECHO OUT CATINV04,LINK,TEMP >> CATINVT ECHO X >> CATINVT ECHO EXPORT IN CATINV04 >> CATINVT ECHO EXPORT DATE YYYYMMDD '/' >> CATINVT ECHO EXPORT HEADING FIELDNAME >> CATINVT ECHO EXPORT OUT CATINVTF >> CATINVT ECHO EXPORT EXIT >> CATINVT ECHO EXIT >> CATINVT ECHO !!EOJ >> CATINVT SAVE CATINVT STREAM CATINVT