Conversion codes are applied to the raw data in a field to transform that data into
a different format. Dates are a good example of where a conversion code should
be used. Internally, all UniData/UniVerse (U2) dates are stored as an integer representing the number
of days relative to Day 0 (in UniVerse, Day 0 is December 31, 1967). When a date
is retrieved from this field, it must be converted to a conventional form that is
recognizable to the user.
You may specify one or more conversion codes by separating
each conversion code by the pipe (|) character. If you use multiple
conversion codes, the codes are applied from left to right, so that the leftmost
code is applied to the original
value, then the next conversion code to the right
is applied to the result of the first conversion, and so on. Generally, conversion
codes are identical to those used in the ICONV and OCONV functions of BASIC.
The following table describes some of the more useful standard conversion codes
and any Kourier conversion code extensions. For a complete list of conversion
codes please consult your U2 documentation.
|
Conversion |
Description - Usage - Notes |
|
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.
|
Conversion |
Result |
Comment |
|
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 |
This conversion will take a string like
12094*43873 where 12094 is an internal date and
43873 is an internal time and convert it to a
format acceptable for SQL date/time field.
See the subroutine
KT.DATETIME
for help on how you can build strings of this
type from multiple attributes. |
|
USQLDATE |
|
|
|
|
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.
|
Conversion |
Result |
Comment |
|
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.
|
Conversion |
Result |
Comment |
|
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.
|
Conversion |
Result |
Comment |
|
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.
|
Conversion |
Result |
Comment |
|
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 |
|
|
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.
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.
|
Conversion |
Result |
Comment |
|
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 preceeded 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 orginal 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.
|
Conversion |
Result |
Comment |
|
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.
|
Conversion |
Comment |
|
@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. |