|
|
|
|
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.
|
|
|
|
|
D2- |
11-17-06 |
|
|
D4- |
11-17-2006 |
|
|
D |
17 NOV 2006 |
|
|
D0 |
17 NOV |
|
|
DD |
17 |
|
|
DM |
11 |
|
|
DY |
2006 |
|
|
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 12094*43873 where 12094 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. |
|
USQLDATE |
17 NOV 2006 |
Converts and internal date into a format acceptable for a SQL
date/time field. |
|
|
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 |
|
|
G1*1 |
456 |
|
|
G1*2 |
456*789:XY |
|
|
G0:1 |
123*456*789 |
|
|
G1:1 |
XY |
|
|
|
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.
|
|
|
|
|
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 is 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 |
|
MR23, |
1,420.19 |
De-scales number by 10, two implied decimal places with
commas |
|
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 we 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. Non-printable characters may be entered
using "0x" notation. |
|
UTRIM |
492 Seacrest Dr. |
Kourier Extension - Trims leading, imbedded redundant, and
trailing spaces |
|
UVLIST{;qch} |
"492 Seacrest Dr." |
Change a comma delimited string into a TCL value list. The
value list may use either the double-quote (default) or single-quote
character. |
|
|
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 |
s 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. |
|
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. |
|
@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 |
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 |
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. |
|
@NV |
Returns the number of values in the source
attribute/dictionary. |
|
@NS |
Returns the number of sub-values in the source
attribute/dictionary. |
|
@RECORD<amc,vmc,svmc> |
Extracts data from the current record indicated by the value of
amc (attribute), vmc (value) and svmc (sub-value). if
specified, all parameters must be numeric. The vmc and svmc
parameters are optional. |
|
@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.
|