/CONDITION - Connect_ETL

Purpose

To define a condition for selection of records or for assignment of different values to a field.

Format

/CONDITION

name expression [{AND | OR} expression ... ]

For use of parentheses within the condition, see Notes below (section "Evaluating a Condition").

expression	=	NOT]	{value_compare } {value } {condition_name}
value_compare	=	value comparison_operator [value]
value	=	{numeric_expression } {text_value [\|\| text_value..] } {bit_value} {date_value } {NULL }
numeric_expression	=	numeric_value [numeric_operator numeric_value...]
numeric_value	=	[+ \| –]	{numeric_field } {numeric_named_value} {number } {function_call } {(numeric_expression)}
numeric_operator	=	{ *} { /} { +} { –}
text_value	=	{text_field } {text_named_value } {text } {function_call }
bit_value	=	{bit_field } {bit_named_value } {bits } {function_call }
date_value	=	{date_field } {date_named_value } {function_call }
function_call	=	function_name([value[,value,...]])
comparison_operator	=	{= } {!= } {< } {> } {<= } {>= } {CT } {NC }	equals does not equal less than greater than less than or equal to/not greater than greater than or equal to/ not less than contains does not contain

Arguments

name	The name of the condition, which you use to reference the condition in other dmexpress options. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
numeric_field	The name of a numeric field defined through /RECORDLAYOUT, /DELIMITEDRECORDLAYOUT, or /DATADICTIONARY. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
numeric_named_value	The name of a numeric value defined through /VALUE
number	A numeric quantity. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help.
text_field	The name of a character field defined through, /RECORDLAYOUT, /DELIMITEDRECORDLAYOUT, or /DATADICTIONARY. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
text_named_value	The name of a character value defined through /VALUE.
text	A character string. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help.
bit_field	The name of a bit field defined through, /RECORDLAYOUT, /DELIMITEDRECORDLAYOUT, or /DATADICTIONARY. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
bit_named_value	The name of a bit value defined through /VALUE.
bits	A bit string. You can specify the bit string in any valid format. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help.
date_field	The name of a date/time field defined through / /RECORDLAYOUT, /DELIMITEDRECORDLAYOUT, /VALUE, or /REFORMAT. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
date_named_value	The name of a date value defined through /VALUE.
function_name	Name of a Connect ETL function that is referenced from within the expression. See Reference/dmexpress Functions Reference in the Connect help for a list of supported functions and their syntax.
condition_name	The name of a condition defined through another /CONDITION option or supplied through the /DATADICTIONARY option.
value_name	The name of a value defined through another /VALUE or supplied through the /DATADICTIONARY option.

Location

The option may appear anywhere in the task definition.

Notes

Evaluating a Condition

A condition consists of one of the following:

A single logical expression, a statement that is either true or false for a particular source record. This may be a condition defined in another /CONDITION option or supplied through /DATADICTIONARY, which may be negated by NOT,
A compound expression, several logical expressions connected by AND and OR,
An expression that results in a number. The condition is false if the value is 0 or NULL, and true otherwise
A bit field or constant of 1 bit.

You may include both single and compound logical expressions in a pair of parentheses. There is no limit to the level of nesting of parentheses.

When evaluating a condition for a source record, Connect ETL evaluates each logical expression separately, yielding a true or false result. Expressions within parentheses are evaluated first. When there is nesting of parentheses (parentheses enclosed within parentheses), the expressions are evaluated outward from the one within the innermost pair of parentheses.

When the order of evaluation is not indicated by parentheses, AND is applied before OR. Multiple ANDs or ORs are applied from left to right. A compound expression consisting of two (or more) logical expressions connected by AND is true when both (all) of its component expressions are true. A compound expression consisting of logical expressions connected by OR is true when at least one of its component expressions is true.

The result of the evaluation for a record is that the condition is either true or false.

Character String Comparisons

When a comparison involves two-character operands, each of the operands can be a character field, a character value defined through /VALUE, a character constant or an expression (concatenation using ‘||’ operator) containing character fields, values, and constants.

Prior to initiating operations with ASCII or EBCIDIC-encoded fields, Connect ETL attempts to convert locale-encoded character text constants to the encoding of the field with which the text constant is compared or concatenated or both. In comparisons between a locale-encoded character text constant and an ASCII-encoded source field or in concatenations, for example, Connect ETL attempts to convert the locale-encoded character text constant to an ASCII-encoded character text constant. In addition, Connect ETL may convert locale-encoded character text constants that serve as arguments in certain dmexpress functions.

While performance may be impacted by encoding conversions, these Connect ETL conversions are designed to avoid the overhead of locale operations and thereby maximize performance.

When a field and a string whose lengths are not equal are compared, the string is truncated or padded on the right with the pad byte to the length of the field. When two-character fields of different lengths are compared, the shorter is padded with the key pad byte.

You may use the contains(CT) and does not contain(NC) operators to do substring matching. For example, the condition field1 ct "abcd" evaluates as true if the string abcd occurs anywhere within the length of field1. The result of evaluating this condition for several records is as follows:

field1 contents	Result of evaluation
abcdefghijkl	T
lkjihgfedcba	F
defgabcdabcd	T
abcabcabcabc	F

Truncation and padding are not done for the contains and does not contain operators.

If either of the operands of sub-string matching is in Unicode, the matching is performed in Unicode.

Numeric Comparisons

When a comparison involves two numeric operands, each of the operands can be a numeric field, a numeric value defined through /VALUE, a number or an arithmetic expression containing fields, values, and numbers. When two operands are combined via an arithmetic operation (addition, subtraction, multiplication or division), the result has a data type 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.

For example, when (amount / 5) < discount is evaluated where amount is a binary integer field, the result of dividing amount by 5 is also an integer. If you want the division to result in a number with a fractional part, output the result of the arithmetic expression in a format that supports fractional digits in /REFORMAT.

Date/time comparisons

When two date/time operands are compared, the date/time components common to the two operands are compared. For example, when transaction_date, a date/time field composed of year, month, day, hour, minute and second, is compared to a reconcile_date, a date/time field composed of month and day, only the month and day components are compared.

Bit String Comparisons

When a bit field and a bit string whose lengths are not equal are compared, the string is truncated on the right or padded on the right with 0 bits to the length of the field. When two-bit fields of different lengths are compared, the shorter is padded on the right with 0 bits to the length of the longer.

The operators contains(CT) and does not contain(NC) are not supported for bit strings.

Comparisons involving null values

You can define a condition to check if the value in a field is NULL by comparing the value with a NULL operand (e.g. field = NULL, field != NULL). If the value is NULL, the equals (=) comparison returns TRUE and the does not equal (!=) comparison returns FALSE. You can also compare two nullable fields. The same logic for comparing a field against a NULL operand applies when value in one or both of the fields is NULL.

A null value compares equal only to another null value or to the NULL operand. A null value does not compare equal to an empty field, e.g. a delimited field with no contents, unless the /NULLIF option has been specified.

For comparisons other than equals and does not equal, such as <=, <, >=, >, CT, NC, among others, the result is always FALSE if either of the operands is NULL.

Examples

/CONDITION USA country ct "USA" or country = "United States"

The option names a condition, USA, defined by a compound logical expression, country ct "USA" or country = "United States". The truth of a compound expression depends on the truth of its components, which in this case are two simple logical expressions, country ct "USA" and country = "United States". The former is true if the field country contains the character string USA. The latter is true if the field country is equal to the character string United States. Since these two expressions are connected by or, at least one of them must be true for the compound expression to be true. If the compound expression evaluates as true, then the condition USA is true. On the other hand, if both simple expressions are false, then the compound expression and the condition are false.

/CONDITION reorder stock_num = 0 or order_num > stock_num

The name of the condition is reorder. It is true if at least one of the compounded expressions, stock_num = 0 and order_num > stock_num, evaluates as true. The expression stock_num = 0 is true if the value of the field stock_num is zero; the expression order_num > stock_num is true if the value in the field order_num is greater than the value in the field stock_num. If both expressions evaluate to false, the named condition, too, is false.

/CONDITION extract (state = "MA" or state = "NH") and rep_id = 215

For the condition extract to be true, both expressions connected by and must evaluate as true. The first expression, state = "MA" or state = "NH", is true if the state field equals the character string "MA" or "NH". The second expression is true if the value in the rep_id field is 215. If either expression fails to evaluate as true, then also their combination and, consequently, the condition extract are false.

/CONDITION no_amount check_total = 0 or check_print ct 5*"*"

The condition no_amount is true if either the check_total field is zero or the check_print field contains five asterisks.

/CONDTION bit2only bit_fl = b"010"

The condition bit2only is true if the first and third bits in the bit_fl field are ’0’ bits and the second bit in the field is a 1 bit.

/CONDITION overdrawn (balance - minimum-balance) < (credit-card-balance + other-debt)

The option names a condition, overdrawn,that compares the results of two arithmetic operations.The contents of the minimum-balance field are subtracted from the contents of balance to yield one number. The contents of the credit-card-balance and other-debt fields are added to yield a second number. When the first number is less than the second number, the condition is true. When the first number is greater than or equal to the second number, the condition is false.

/CONDITION is_client_name_present productiondb.client.client_name != null

This name of the condition is is_client_name_present. It is true if there is a non-null value in the column client_name in the source database table client accessed via the database connection productiondb.

/CONDITION positive_balance left_record.item_cost < right_record.account_balance

The condition positive_balance is used for an evaluation in a join task after the records have been joined. For positive_balance to be true, the contents of the item_cost field in the portion of the joined record originating from the left side of the join must be smaller than the contents of the account_balance field in the portion of the joined record originating from the right side of the join.

/CONDITION zero_amount check_total = 0 
/CONDITION positive_balance (check_total – expenses) >= 0 
/CONDITION valid_amount (NOT zero_amount) or(positive_balance)

The condition valid_amount determines if an amount is valid based on the results of evaluating two other conditions. Note that the result of the condition zero_amount is negated before being used to evaluate the condition valid_amount.

/CONDITION always_true 1

The condition always_true is always true.

/CONDITION valid_amount report_layout.amount

The condition valid_amount is true if the value in the field report_layout.amount is not zero or NULL.

/CONDITION valid_amount IsValidNumber(amount)

The condition valid_amount is true if the function IsValidNumber returns 1 after validating the contents of the field amount.

/CONDITION non_zero_balance balance.amount + balance.deposit – balance.withdrawal

The condition non_zero_balance is true if the arithmatic expression results in a numeric value which is not zero.

/CONDITION - Connect_ETL - 9.13

Connect ETL Data Transformation Language (DTL) Guide