|
|
|
|
Date
|
The date conversion code is used to convert internal dates to external formats. Assuming an internal date of 14201, the following conversion codes will produce the result shown.
All conversions can specify optional delimeter {;delim}.
|
|
|
|
|
D2-
|
11-17-06
|
Two-digit year conversion code. Other delimiters can be specified (such as D2/ for slash delimiters).
|
|
D4-
|
11-17-2006
|
Four-digit year conversion code. Other delimiters can be specified (such as D4/ for slash delimiters).
|
|
D
|
17 NOV 2006
|
U2 Default Date Conversion
|
|
D0
|
17 NOV
|
U2 Default Date Conversion with no year
|
|
DD
|
17
|
Two-digit day of the date
|
|
DM
|
11
|
Two-digit month of the date
|
|
DY
|
2006
|
Four-digit year of the date
|
|
DWA
|
FRIDAY
|
Day of the week
|
|
DJ
|
321
|
Julian date - the 321st day of the year
|
|
UYYYYMMDD
|
20061117
|
Kourier Extension - 4-digit year, 2-digit month, 2-digit day
|
|
UYYMMDD
|
061117
|
Kourier Extension - 2-digit year, 2-digit month, 2-digit day
|
|
UYYYYDDMM
|
20061711
|
Kourier Extension - 4-digit year, 2-digit day,2-digit month
|
|
UYYDDMM
|
061711
|
Kourier Extension - 2-digit year, 2-digit day,2-digit month
|
|
UMMDDYYYY
|
11172006
|
Kourier Extension - 2-digit month, 2-digit day, 4-digit year
|
|
UMMDDYY
|
111706
|
Kourier Extension - 2-digit month, 2-digit day, 2-digit year
|
|
UDDMMYYYY
|
17112006
|
Kourier Extension - 2-digit day,2-digit month, 4-digit year
|
|
UDDMMYY
|
171106
|
Kourier Extension - 2-digit day,2-digit month, 2-digit year
|
|
USQLDATETIME
|
17 NOV 2006 12:00:00
|
Convert a string like 14201*43873 where 14201 is an internal date and 43873 is an internal time to a format acceptable for a SQL date/time field. See the subroutine KT.DATETIME for help on how you can build strings of this type from multiple attributes. Any non-numeric delimiter can be used to separate date and time, except dash (“-“) and dot (“.”).
|
|
USQLDATETIMEH
|
17 OCT 2006 12:11:13pm
|
Convert a string like 14201*43873 where 14201 is an internal date and 43873 is an internal time to a format acceptable for a SQL date/time field. As USQLDATETIME, except it will display conventional time format with AM or PM designator instead of military time format.
|
|
USQLDATE
|
2006-
|
Converts and internal date into a format acceptable for a SQL date/time field. Optionally add a date delimiter to change from the default dash delimiter (“-“).
|
|
UISODATE
|
2006-11-17
|
Converts an internal date into ISO format date. Optionally add a date delimiter to change from the default dash delimiter (“-“).
|
|
UIFDATE
|
14201
|
If internal date is within acceptable date parameters, this conversion code returns the value passed. If not within acceptable parameters, it returns null. Optionally specify minimum and maximum dates. Default minimum date is 01-01-1753, default maximum date is 12-31-9999.
|
|
|
Time
|
The time conversion code is used to convert internal times to external formats. Assuming an internal time of 50000, the following conversion codes will produce the result shown.
|
|
|
|
|
MT
|
13:53
|
Hours and minutes in military format
|
|
MTS
|
13:53:20
|
Hours, minutes and seconds in military format
|
|
MTH
|
01:53pm
|
Hours and minutes in standard format
|
|
UHHMMSS
|
135320
|
Kourier Extension - Hours, minutes and seconds in military format without colons
|
|
UTICKS
|
00:00:50.000
|
Converts internal time stored in milliseconds to hours, minutes, seconds and milliseconds
|
|
UTICKSZ
|
50.000
|
Converts internal time stored in milliseconds to hours, minutes, seconds and milliseconds. Leading zeros are suppressed.
|
|
|
Group Extract
|
The group extract conversion code is used to extract one or more values from a string separated by a given delimiter. Assuming a string of 123*456*789:XY, the following conversion codes will produce the result shown.
|
|
|
|
|
G0*1
|
123
|
Return one segment of the string starting at the first position, as delimited by asterisk.
|
|
G1*1
|
456
|
Return one segment of the string starting at the second position, as delimited by asterisk.
|
|
G1*2
|
456*789:XY
|
Return two segments of the string starting at the second position, as delimited by asterisk.
|
|
G0:1
|
123*456*789
|
Return one segment of the string starting at the first position, as delimited by colon.
|
|
G1:1
|
XY
|
Return one segment of the string starting at the second position, as delimited by colon.
|
|
|
Numbers
|
The MR or MD conversion codes are used to convert internal numbers to external formats. Assuming an internal number of 1420190, the following conversion codes will produce the result shown.
On Universe, using MR will force a null value to zero, thus alleviating the need to use a substitution conversion code (“S” code) to return a zero for nulls.
|
|
|
|
|
MR2
|
14201.90
|
Number has two implied decimal places
|
|
MR4
|
142.0190
|
Number has four implied decimal places
|
|
MR2,$
|
$14,201.90
|
Displays the number as currency with commas
|
|
MR(%10)
|
0001420190
|
Displays the number zero filled in ten characters
|
|
MR23,
|
1,420.19
|
De-scales number by 10, two implied decimal places with commas
|
|
UEBCDIC
|
142019}
|
Kourier Extension - Converts negative numbers to EBCDIC format
|
|
UINVERSE
|
-1420190
|
Kourier Extension - Returns the inverse of a number
|
|
|
Characters
|
The mask character conversion codes are used to select certain types of characters out of a string. Assuming a character string of 492 Seacrest Dr., the following conversion codes will produce the result shown.
|
|
|
|
|
MCU
|
492 SEACREST DR.
|
Converts all characters to upper case
|
|
MCA
|
SeacrestDr
|
Extracts all alphabetic characters
|
|
MCN
|
492
|
Extracts all numeric characters
|
|
MX
|
3439322053656163726573742044722E
|
Displays the hexadecimal representation of the ASCII characters in the string
|
|
UDECRYPT
|
{left blank for security reasons}
|
Decrypt a string that was encrypted using UENCRYPT.
|
|
UENCRYPT
|
{left blank for security reasons}
|
Encrypt a string. Note: the length of the encrypted string will be twice the length of the source string.
|
|
UCHANGE;str1;str2
|
492 SEacrEst Dr.
|
Change str1 to str2. Change strings may contain one or more printable characters. In this example, the conversion code is changing lower case “e” to upper case “E” (UCHANGE;e;E).Non-printable characters may be entered using "0x" notation.
|
|
UTRIM
|
492 Seacrest Dr.
|
Kourier Extension - Trims leading, embedded redundant, and trailing spaces
|
|
UTRUNCATE;length{;str}
|
492 Seacrest Dr.
|
Truncates the data at the length specified. The length must include the trailing indicator characters. The ellipsis ("...") is the default indicator, which adds 3 characters to the length.including the characters "...". This makes is easier to identify truncated data in the target application. Use the optional str to specify a different set of characters other than "...".
|
|
UVLIST{;qch}
|
"492 Seacrest Dr."
|
Change a comma delimited string into a TCL value list, with each value surrounded by quotes.. The value list may use either the double-quote (default) or single-quote character (where qch is the single-quote character).
|
|
UXML
|
"492 Seacrest Dr."
|
Converts the 5 ASCII characters (", ', <, >, &) that must be escaped in an XML string.
|
|
UBOOLEAN;str
|
true or false
|
Compares the value against the value sent in the str expression. If they are equal, it returns “true”. If they are not equal, it returns “false”.
|
|
|
Substitution
|
The substitution conversion code allows you to specify simple if/then processing on a data value. The format of the substitution conversion code is S;op1;op2.
|
|
|
|
op1
|
Is the value to use if the field is not null. If op1 is numeric, then the value is returned from the current record (if the value is greater than zero) or from the item-id of the current record (if the value is zero). If op1 is a delimited text string, that value is returned. If op1 is an asterisk, the original value is returned. Example: S;*;0 would return the original value if not null, or zero if null.
|
|
op2
|
Is the value to use if the field is null. If op2 is numeric, then the value is returned from the current record (if the value is greater than zero) or from the item-id of the current record (if the value is zero). If op2 is a delimited text string, that value is returned. If op2 is an asterisk, the original value is returned.
|
|
|
Text Extract
|
The text extract conversion code is used to extract a certain number of characters from a text string. Assuming a character string of 492 Seacrest Dr., the following conversion codes will produce the result shown.
|
|
|
|
|
T1,3
|
492
|
Extracts three characters beginning at position 1
|
|
T5,3
|
Sea
|
Extracts three characters beginning at position 5
|
|
|
Translate
|
The Tfile conversion code converts values from one file to another by translating through a file. It uses data values in the source file as IDs for records in a lookup file. The source file can then reference values in the lookup file. To access the lookup file, its record IDs (field 0) must be referenced. If no reference is made to the record IDs of the lookup file, the file cannot be opened and the conversion cannot be performed.
The format of the translate conversion code is: Tfilename ; c [vmc]; [ivmc] ; [ovmc].
|
|
|
|
filename
|
The name of the lookup file. It can be preceded by the keyword DICT to reference the files dictionary.
|
|
c
|
Translate subcode. When used in Kourier, you should only use the C (if conversion is impossible, return the original value) and X (if conversion is impossible, return an empty string). The translate subcode may be followed by a number indicating which value to return from a multivalued field.
|
|
ivmc
|
When used in Kourier, this field should always be omitted.
|
|
ovmc
|
Indicates the attribute number in the lookup file to return
|
Examples:
|
|
|
|
|
TCUSTOMER;X;;1
|
Acme Inc.
|
Assuming the current data value is the item-id to the Customer file, the value in attribute 1 is returned, presumably the Customer name.
|
|
UITRANS;lookup_key
|
|
Integration translate replaces the current value with the value found in the lookup table referenced by the lookup_key. The lookup_key is the item-id of the record in the KT_INTEGRATION_XREF file. For example, UITRANS;DEMO*LOG_ERR_TYPE will use the lookup table named DEMO*LOG_ERR_TYPE when processing data values.
|
|
|
@ Codes
|
Conversion codes that being with an @ character provide special capabilities. The following table indicated the supported @ codes. Optional parameters are enclosed in curly braces {} and are not part of the conversion syntax.
|
|
|
|
@DEDUP{;sort;allow-nulls}
|
Removes all duplicate values in a multi-valued field. Use the optional sort expression to control the order in which the multi-values are returned. If specified, sort can be null (natural order), AR (ascending right), AL (ascending left), DR (descending right) or DL (descending left). If sort is not specified, the multi-values are returned in natural order. Use the optional allow-nulls flag to indicate if null values should be included in the output. A value of Y indicates that nulls should be included while a value of N indicates that nulls should not be included. If allow-nulls is not specified, nulls will be included.
|
|
@ISAMV
|
Forces Kourier to treat the field as a MultiValue field.
|
|
@ISAMS
|
Forces Kourier to treat the field as a MultiSubValue field.
|
|
@LASTCHGBY
|
Returns the operating system name of the last user to change a record. This conversion is only valid when an export has been net change enabled.
|
|
@LASTCHGDATE
|
Returns the date (in internal format) of when the records was last changed. This conversion is only valid when an export has been net change enabled.
|
|
@LASTCHGDATETIME
|
Returns the date and time (in 12094*43873 format - see USQLDATETIME conversion) of when the records was last changed. This conversion is only valid when an export has been net change enabled.
|
|
@LASTCHGTIME
|
Returns the time (in internal format) of when the records was last changed. This conversion is only valid when an export has been net change enabled.
|
|
@LOCATE;lookfor;lookinAMC;returnAMC
|
Searches all of the multi-values in the lookinAMC attribute for the string specified in the lookfor parameter and returns the data found in the matching value position in the returnAMC attribute. If the lookfor parameter is not found in the lookinAMC then an empty string is returned.
|
|
@MAXNV;n;n{;n…}
|
Returns the number of values in the source attributes referenced. Attributes are delimited by the semi-colon (;) character. This substitution is useful when a single attribute can not be identified as the controlling attribute in an association.
|
|
@MAXNS;n;n{;n…}
|
Returns the number of sub-values in the source attributes referenced. Attributes are delimited by the semi-colon (;) character. This substitution is useful when a single attribute can not be identified as the controlling attribute in an association.
|
|
@NOENCODE
|
This conversion inhibits the encoding of reserved characters when generating XML, HTML or JSON output for an export field. It is used when the application programmer wants absolute control over the output returned in an export field (e.g. injecting results from an external API or building complicated XML, HTML or JSON).
If the output format is JSON, make sure to return a value of ”” if the output is null as shown in the UniBASIC code below:
IF FIELD$ = '' AND RMLO.EXPORT.FORMAT$ = 'JSON' THEN FIELD$ = '""'
This ensures that the JSON created can be validated by a JSON parser.
|
|
@NV
|
Returns the number of values in the source attribute/dictionary.
|
|
@NS
|
Returns the number of sub-values in the source attribute/dictionary.
|
|
@STRUCT;n
|
In some applications, the structure of associated multi-valued attributes are not maintained in parallel (i.e. the same number of values/sub-values in each attribute). This conversion code forces the source attribute to follow the structure of the attribute reference by 'n'.
|
|
@TEXT{;str}
|
Converts all system delimiters (@AM, @VM, @SVM, and @TM) in the source attribute/dictionary to the space character. Use the optional str expression to choose a character (or string of characters) other than the space character. Non-printable characters may be entered using "0x" notation.
|
|
@TM2SPC{;str}
|
Converts the @TM system delimiter in the source attribute/dictionary to the space character. Use the optional str expression to choose a character (or string of characters) other than the space character. Non-printable characters may be entered using "0x" notation.
|
|
@SVM2SPC{;str}
|
Converts the @SVM system delimiter in the source attribute/dictionary to the space character. Use the optional str expression to choose a character (or string of characters) other than the space character. Non-printable characters may be entered using "0x" notation.
|
|
@VM2SPC{;str}
|
Converts the @VM system delimiter in the source attribute/dictionary to the space character. Use the optional str expression to choose a character (or string of characters) other than the space character. Non-printable characters may be entered using "0x" notation.
|
|
|
Literal Strings
|
To specify a literal string, enclose the data value in quotes or double quotes.
For example, entering "{@YYYYMMDD}" (including the quotation marks) will return the current system date in YYYYMMDD format. Entering "KOURIER" (including the quotation marks) will return the string KOURIER.
The string can specify any number of valid substitution tags enclosed by curly braces ( {} ).
|
|
"0x" Notation
|
For most conversions that allow a string ("str") to be specified, you may specify non-printable characters (or any characters) using the prefix "0x".
The string "0x0D0A" would specify the carriage return and line-feed characters.
If you would like to specify the pipe character in a string use "0x7C". For example, the conversion "@TEXT;0x7C|MCU" will convert system delimiters to the pipe character and then upcase the entire string.
|