Purpose
To derive a value from the contents of other source fields, values, and/or constants.
Format
/VALUE name expression
where
| expression | = | {value_name } {field_name } {constant } {NULL } {numeric_expression} {text_concatenation} {function_call } |
| constant | = | {text } {number} {bits } |
| numeric_expression | = | numeric_operand [operator numeric_operand...] |
| numeric_operand | = | [+ | –] expression |
| operator | = | { *} { /} { +} { –} |
| text_concatenation | = | text_operand [|| text_operand...] |
| text_operand | = | expression |
| function_call | = | func_name([expression[,expression,...]]) |
Arguments
| name |
The name of the value, which is used to reference the value in other dmexpress options. The name you give must adhere to the rules described for an identifier. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help. |
| value_name | The name of a value referenced in the expression. |
| field_name | The name of a field in the source record referenced in the expression. |
| func_name |
Name of a dmexpress function, which is invoked to obtain a value or execute operations. See Reference/Connect ETL Functions Reference in the Connect help for a list of supported functions and their syntax. |
Location
This option may appear anywhere in the task definition.
Default Data Type and Length
Connect ETL attempts to determine a default data type and length for the value when it is output to a file without explicit format conversion. The default is determined from the value definition as follows:
When the definition is not an arithmetic expression, text concatenation, or dmexpress function call, the data type and length are the same as that of the source.
When the source is another field, the derived value has the same data type and length as the source field.
When the source is an integer number, the derived field is an unsigned decimal for a positive number and a leading separate sign decimal for a negative number. The length is sufficient to hold the number of significant digits in the integer.
When the source is a floating-point number, the derived field is edited numeric. The length is sufficient to hold a sign, the number of significant digits before the decimal point, and, when there are non- zero digits after the decimal point, the decimal point and the following significant digits.
When the definition is an arithmetic expression, Connect ETL determines the default data type and length from the values appearing in the expression. The data type of the result that is dependent on the data type of the two operands. For a summary of valid data types and formatting conventions, see Data types reference in the Connect help. The length of the result is sufficient to hold the larger of the two operands.
When the definition is a text concatenation, the data type of the result is character. The length of the result is the total length of the two operands.
When the definition is a dmexpress function call, the resulting data type and length varies depending on the function. See Reference/Connect ETL Functions Reference in the Connect help for a list of supported functions and their syntax.
Nullable values
If the format of a null value is not specified explicitly, it does not have an external format and it is compatible with every other external format When the data type or length of a nullable value is changed, the resulting value is also nullable.
If any operand of an operation is NULL, the result is NULL except for string concatenation. In string concatenation, a NULL value is treated as an empty string.
Example
/VALUE country IfThenElse(in_usa, "USA", "outside USA")/VALUE cold "COLD"/VALUE print_date TODAY(‘mm/dd/year hh:mi0’)The value print_date contains a value dependent on the current system date. The result of applying the edit mask is left justified in the field. The editing consists of creating a value containing the current month in numeric form with any leading zero removed (e.g. 3 for March, 10 for October), followed by a slash, the current day of the month with any leading zero removed, another slash, and the four digit year. The date portion is followed by a space and the time portion, consisting of the current hour from the 24-hour clock with leading zero removed, a colon and the current minute with no zero removed. For example, 3 o’clock in the afternoon of February 14, 1998 appears as 2/14/98 15:00 with a single trailing space. For information on date and time mask formats, see the Syntax of date pattern topic in the Connect help.