Over the years we've grown the if/extract function list, to the point where it needs it's own documentation for both the if and extract commands. Functions can be used in both the if and extract commands, however, some functions were sometimes written to either be specifically used in either if or extract, but typically have an application in both.
UC4 is a scripting/scheduling/job system found on many platforms and is common on HP-UX. It also resolves environment variables for you when it runs your scripts.
Suprtool has a number of functions that require a "$" sign in front of them making them look like variables. This causes an issue with UC4 as it tries to resolve any variables. So if a script has a $lookup in it and $lookup is not a variable then the $lookup is removed, for example what would happen is:
if $lookup(mytable,key)becomes:
if (mytable,key)
In order to rectify this all that needs to happen is you "Escape Out" the Environment Variable from being resolved by changing the script to be:
if \$lookup(mytable,key)
When you do this UC4, (and HP-UX for that matter), now knows that the $lookup is not a token that needs to be resolved as an environment variable but the $lookup is a literal.
Suprtool has a number of functions that work on String/Byte type fields. In all cases the target and source of these functions are byte type fields and are treated as strings internally to Suprtool.
Purpose is to remove spaces from the beginning and the end of a field.
If Usage:if $trim(last_name) = "ARMSTRONG"
This means that Suprtool will qualify "ARMSTRONG", " ARMSTRONG", " ARMSTRONG" etc.
Extract Usage (target: Byte type fields)EXT byte_target=$trim(byte_source)Example:
DEF lastname,1,16,byte EXT lastname=$trim(last_name)Data Examples Before and After:
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
ext lastname=$trim(lastname) >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
Purpose is to remove spaces from the left side of the field.
If Usage:if $ltrim(first_name) = "NEIL "Extract Usage (target: Byte type fields)
ext byte_target=$ltrim(byte_source)Example:
DEF lastname,1,16,byte EXT lastname=$ltrim(last_name)Data Examples before and after:
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
ext lastname=$ltrim(lastname) >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
Purpose is to remove spaces from the right side of the field.
If Usage:if $rtrim(first_name) = "Neil"Extract Usage (target: Byte type fields)
extract byte_target=$rtrim(byte_source)Example:
def lastname,1,16,byte ext lastname=$rtrim(last_name)Data Examples:
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONGbb
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
It is difficult to show what $RTRIM does in a simple instance, however, it is perfect for constructing/merging two separate fields into on as in:
ext fullname=$rtrim(first-name) + " " + $trim(last-name)Data Result
Neil Armstrong
Purpose is to make all relevant bytes "Upper" case.
If Usage:if $upper(first_name) = "NEIL"Extract Usage (target: Byte type fields)
extract byte-target=$upper(byte-source)Example:
def lastname,1,16,byte ext lastname=$upper(last_name)Data Examples:
>ext lastname=$upper(lastname) >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = ARMSTRONG
Purpose it to make all relevant bytes "Lower" case.
If Usage:if $lower(first_name) = "neil"Extract Usage:
ext byte_target=$lower(byte_source)Example:
def newfirstname,1,16,byte ext newfirstname=$lower(first_name)Data Examples:
>ext lastname=$lower(lastname) >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) LASTNAME = armstrong
Purpose is to make the relevant bytes either Upper or Lower Case in an intelligent manner. It will Upshift the first character in a field and anything following a space or dash.
If Usage:if $proper(first_name) = "Neil"Extract Usage:
ext byte_target=$proper(byte_source)Example:
def fullname,1,30,byte ext fullname=$proper(first_name)Data Examples:
>ext fullname=$proper(fullname) >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) FULLNAME = Neil Armstrong
Purpose is to extract a string into a byte type field that begins at a certain character instance and ends and a second character instance. Commonly used to reformat name fields and read .CSV files.
If Usage:Not that commonly used.Extract Usage:
extract field=$split(field_from,start_pos,instance,end_pos,instance)
define acct-x,1,12,byte define fullname,1,30 ext acct-x=$split(record,first,",") ext fullname=$split(record,",",",")Data Examples:
Source Data of record acct-x result 123456789,"Neil Armstrong",435 123456789Data Examples:
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) ACCT-X = 123456789 >IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) FULLNAME = "Neil Armstrong"
Purpose is to find any specified character(s) in any byte string fields anywhere in a given byte field.
If Usage:clean "^10" if $findclean(e-mail)
Records will qualify if a Line Feed is in the e-mail field.
Purpose was to "clean" and remove unwanted characters from any byte-string fields in any point in the field. Typically used to removed un-printable characters from byte fields.
If Usage: (Not commonly used)clean "N" if $clean(first_name)=" eil"Extract Usage:
extract byte_target=$target(byte_source)Example:
clean "N" set cleanchar " " extract byte_target=$clean(byte_source)Data Examples:
Source: Neil Target: eil
Example, remove Line Feed from E-mail Address field and shift data to the left:
Clean "^10" set cleanchar "<null>" ext e-mail=$clean(e-mail)
Purpose to translate any character from a byte type field to any defined byte, primarily to obfuscate data and was primarily designed for the extract command,.
If Usage:
translate "A:C"
if $translate(Full_Name)="Neil Crmstrong" { not commonly used }
Extract Usage:
translate "A:C" translate "B:D" translate "a:f" extract New-byte-field=$translate(byte-field)Data Examples: (Using above code)
Source Data: Result Data Barry Armstrong Dfrry Crmstrong
Purpose to Justify the field on the Left side of the byte field.
If Usage:if $JUSTYIFYL(Full_Name)="Neil Armstrong"Extract Usage:
extract NewByteField=$justifyl(byte-field)Data Examples: (Using above code)
Source Data: Result Data Barry Armstrong Barry Armstrong
Purpose to Justify the field on the Right side of the byte field.
If Usage:if $JUSTYIFYR(Full_Name)=="@Neil Armstrong"Extract Usage:
extract NewByteField=$justifyr(byte-field)Data Examples: (Using above code)
Source Data: Result Data Barry Armstrong Barry Armstrong
Purpose to add leading zeroes to a particular field with an option to justify the data
If Usage:if $LEADZEROB(NUMDATA,J)="0000006"Extract Usage:
extract NewNumField=$leadzerob(byte-field,J) extract NewNumField=$leadzerob(byte-field,N)Data Examples: (Using above code)
Source Data: Result Data
6 0000006
4 000004
Purpose to Respace a particular byte field.
If Usage:if $RESPACE(NAME,J)="Neil Armstrong"Extract Usage:
extract NewName=$respace(Name,J) extract NewName=$respace(Name,N)Data Examples: (Using above code)
Source Data: Result Data This is some data This is some data This is some data This is some data
Purpose is to convert Ebcidic to Ascii, for a byte type field.
Extract Usage:Extract $etoa(char-field)
Purpose is to convert Ascii to Ebcidic, for a byte type field.
Extract Usage:Extract $atoe(char-field)$etoa and $atoe cannot be used in if command, nor do they put a result in a field, they just extract the converted byte-type field.
Suprtool is capable of doing string addition, again this is with byte type fields, you can do add various byte type fields together and even the results of various functions from above.
Extract Usage:extract target_byte_field = byte-field1 + " " + byte-field2 extract FullName = $trim(first_name) + ' ' + $trim(last_name)Example:
extract FullName = $trim(first_name) + ' ' + $trim(last_name)Data Result:
>IN STRINGS.NEIL.GREEN (0) >OUT $NULL (0) FULLNAME = "Neil Armstrong"
Purpose is to not round a given result or arithmetic expression or number. Suprtools default behaviour is to round a result, the $truncate function will change that behaviour.
If Usage:if $truncate((qty * price) / 100 = 100Extract Usage:
extract new_price=$truncate((qty * price) / 100)
Purpose is to return the absolute value of a given number.
IF Usage:if $abs(credit-field)=5000Extract Usage:
extract newnum=$abs(credit-amt)
Purpose is to provide a running total for any numeric field and deposit the result into a packed-decimal field.
IF Usage:Not commonly used in ifExtract Usage:
define mytotal,1,18,packed ext mytotal=$total(sales-amount)
Purpose is to provide a running total for any numeric field and deposit the result into a packed-decimal field. The target packed-decimal field is reset to 0 on a control break on the specified sort field.
IF Usage:Not commonly used.Extract Usage:
define target,1,18,packed sort sort-field ext target=$subtotal(fieldtototal,sort-field)Example of $TOTAL and $SUBTOTAL
>in file1sd.suprtest >def mytotal,1,14,packed >def mysubtotal,1,14,packed >sort char-field >ext char-field >ext int-field >ext mytotal=$total(int-field) >ext mysubtotal=$subtotal(int-field,char-field) >list >xeq >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (0) CHAR-FIELD = 11111 INT-FIELD = 1111 MYTOTAL = 1111 MYSUBTOTAL = 1111 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (1) CHAR-FIELD = 22222 INT-FIELD = 2222 MYTOTAL = 3333 MYSUBTOTAL = 2222 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (2) CHAR-FIELD = 22222 INT-FIELD = 2222 MYTOTAL = 5555 MYSUBTOTAL = 4444 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (3) CHAR-FIELD = 33333 INT-FIELD = 3333 MYTOTAL = 8888 MYSUBTOTAL = 3333 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (4) CHAR-FIELD = 33333 INT-FIELD = 3333 MYTOTAL = 12221 MYSUBTOTAL = 6666 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (5) CHAR-FIELD = 33333 INT-FIELD = 3333 MYTOTAL = 15554 MYSUBTOTAL = 9999
Purpose is to provide a running counter starting from 1, and the target/result field is a double integer.
IF Usage:Not commonly used.Extract Usage:
define mycounter,1,4,double ext mycounter=$counter
Purpose is to provide a running counter starting from 1, and the target/result field is a double integer. The counter is reset when the control-break occurs on the specified sort field.
IF Usage:Not commonly usedExtract Usage:
define mysubcount,1,4,double sort customer-no ext mysubcount=$subcount(customer-no)Examples for $counter and $subcount:
in file1sd.suprtest def mycount,1,4,double define mysubcount,1,4,double sort char-field ext char-field ext mycount=$counter ext mysubcount=$subcount(char-field) list xeq >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (0) CHAR-FIELD = 11111 MYCOUNT = 1 MYSUBCOUNT = 1 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (1) CHAR-FIELD = 22222 MYCOUNT = 2 MYSUBCOUNT = 1 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (2) CHAR-FIELD = 22222 MYCOUNT = 3 MYSUBCOUNT = 2 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (3) CHAR-FIELD = 33333 MYCOUNT = 4 MYSUBCOUNT = 1 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (4) CHAR-FIELD = 33333 MYCOUNT = 5 MYSUBCOUNT = 2 >IN FILE1SD.SUPRTEST.GREEN >OUT $NULL (5) CHAR-FIELD = 33333 MYCOUNT = 6 MYSUBCOUNT = 3
When the target of an extract conversion is a packed or display- type field, Suprtool always converts positive values to a neutral number. To ensure that expressions with positive values have a positive result, use the $signed function:
IF Usage:Not commonly used:Extract Usage:
extract packed-field=$signed(int-field) extract display_field=$signed(dbl-field / 10)
Used to add leading zeroes to a display field, with an option to justify the numbers or not.
IF Usage:if $leadzeroz(display,J)=6Extract Usage:
def dispnum,1,8,display extract dispnum=$leadzeroz(display,J) extract dispnum=$leadzeroz(display,N)Data Examples:(Using above code)
Source Data: Result Data
6 00000006
4 000004
+ - * / mod
Suprtool has the ability to perform any and all Arithmetic functions on numeric fields.
If Usage:if sales-total <> (sales_qty * unit_price) + sales_taxExtract Usage:
ext sales-total=(sales_qty * unit_price) + sales_tax
Purpose is to convert a freeform number, to a number that can be put into an actual numeric field. The target must be numeric and the source field must be a display type field, even though it can have non numeric characters in it.
If Usage:if $number(disp-field)=500Extract Usage:
define disp-field,byte-field,display {define byte as display with same length}
define newdouble,1,4,double
ext newdouble=$number(disp-field)
Data Examples:
$5.00 ---> 500
Notes: Suprtool will try and match the decimal places of the defined by the item command of the numeric target to the decimal places in the actual data and handle the data properly. For example if the target field has an item command definition of two decimal places, Suprtool will handle the data Raw data accordingly:
Raw Data Result of $number 5.1 5.10 5.123 5.12 5.139 5.14
if $edit(somefield,"$$$$.99-")="$5.00" {Not commonly used}
Extract Usage:
ext byte-field=$edit(dbl-field,"$$$$.99-") ext byte-field=$trim($edit(dbl-field,"$$$$.99-"))Data Examples:
500 $5.00
Purpose is to determine if a key value has been loaded into memory with options to reference associated data.
If Usage:if $lookup is capable of determining if a key value exists and also compare against a field specified with the data field.
table tablename,key,file,filename if $lookup(tablename,key) table tablename,key,file,filename,data(data-field) if $lookup(tablename,key,data-field) = inputsrcdatafieldExtract Usage:
table tablename,key,file,filename,data(data-field) extract target-field=$lookup(tablename,key,data-field)
Purpose is to return true or false if an Oracle field has Nullness.
open oracle suprtest suprpass select * from file1 if $null(somefield)
Purpose is to expand the command line limit for an if command beyond 256 characters.
If Usage:if $read -number=1 and -number=3 and -//
Used to reference the input record number for a given input source.
IF Usage:if char-field="55555" and $inrecnum=123Extract Usage:
def recnum,1,4,double extract recnum=$inrecnum
Purpose is to determine the current date.
If Usage:If date-field=$today(-1) if date-field=$todayExtract Usage:
ext target-date=$todayThe date-field and target-date must be defined as having a particular date format (item command) and in a proper container for that particular date type.
Purpose is to determine a given date.
If Usage:if date-field=$date(*/*/*)Extract Usage:
ext date-field=$date(*/*/*)Date-field must be defined as having a particular date format (item command) and in a proper container
Purpose is to determine if a given date is valid or not.
If Usage:item check-date,date,ccyymmdd if $invalid(check-date)Extract Usage: Not typically used in extract.
Purpose is to convert from any supported Suprtool date in any container to CCYYMMDD in a double integer container. For date formats with only two digit years Suprtool will look at the value of Set Date Cutoff in order to determine what century to convert it to, whether it be 19 or 20.
If Usage:item my-yymmdd-date,date,yymmdd if $stddate(my-yymmdd-date) = 20141213Extract Usage:
item my-yymmdd-date,date,yymmdd define new-ccyymmdd-date,1,4,double ext new-ccyymmdd-date=$stddate(my-yymmdd-date)
Purpose is to convert any supported Suprtool date to a number of days since 4713BC or Julian Day format. Main purpose in if is to compare two dates and get the diffference. Main purpose in extract is to typically transform a date to be so many days away from the current date value.
If Usage:if ($days(entry-date) - $days(order-fulfill-date)) >=30Extract Usage:
extract new-date=$stddate($days(entry-date) + 10)
Purpose is to convert convert a data to a number of months in the future or past.
If Usage:item mydate,date,ccyymmdd if $month(mydate,3) = 20170412Extract Usage:
item mydate,date,ccyymmdd define new-ccyymmdd-date,1,4,double ext new-ccyymmdd-date=$month(mydate,3)
Purpose is to convert any supported Suprtool date to a number of days since 4713BC or Julian Day format. Main purpose in if is to compare two dates and get the diffference. Main purpose in extract is to typically transform a date to be so many days away from the current date value.
If Usage:if ($days(entry-date) - $days(order-fulfill-date)) >=30Extract Usage:
extract new-date=$stddate($days(entry-date) + 10)