Arithmetic |
- |
Subtract to find the difference of |
* |
Multiplied by |
/ |
Divided by |
+ |
Sum of
Note: For the Transformer process, you can select the Use Legacy Plus
Operator option on the Transformer Configuration Output Settings
window to use the plus operator (+) to concatenate multiple arguments
(strings, attributes, and integers) with no space between each argument.
This option is to be used only for backwards compatibility with
Quality projects created in pre-v15 versions of TSS.
|
Comparison |
< |
Less than |
<= |
Less than or equal to |
!=, <> |
Not equal to |
= |
Is equal to |
> |
Greater than |
>= |
Greater than or equal to |
Logical |
AND |
A conjunction between two statements. Both statements must be true for
the conjunction to be true.
- Syntax
-
attribute1 = value1 AND attribute2 = value2
where
attribute1 = value1 is the first statement to be
joined.
attribute2 = value2 is the second statement to be
joined.
Note the following guidelines:
- AND can also be used with the OR and NOT operators.
- Expressions are evaluated from left to right. Precedence is
implied by the order shown in the expression, although
parenthetical statements always take priority.
- Any comparison operator can be used to join statements.
- Statements are not case-sensitive.
- Example
-
- The following associates the values tomato and fruit to
attributes Name and Type: Name = "tomato" AND Type = "fruit"
|
IF, THEN, ELSE |
Logical condition operators. The ELSE statement is optional. When using
the Expression Builder inside a Quality project, do not select "IF" to
specify a condition. The "IF" part of the syntax is automatically
generated.
- Syntax
-
IF expression1 THEN expression2 ELSE
expression3 |TRUE|FALSE
where expression is the condition or argument that
completes the IF, THEN, ELSE logic. The ELSE statement
takes an expression or the keywords "TRUE" or "FALSE".
- Example
-
The following validates the value in the Auth Id Rep attribute:
IF [Status] <> "AUTH" AND [Status] <> "REJECT"
THEN [Auth Id Rep] = "NSP"
ELSE TRUE
|
IF, THEN, ELSE IF, THEN, ELSE |
Logical condition operators. Syntax is similar to the IF, THEN, ELSE
logical statement.
- Syntax
-
IF expression1 THEN expression2 ELSE IF
expression3 THEN expression4 ELSE
expression5 |TRUE|FALSE
where expression is a condition or argument that
completes the IF, THEN, ELSE IF, THEN, ELSE logic. The
ELSE statement takes an expression or the keywords "TRUE" or
"FALSE".
- Example
-
The following validates the value in the Acc Type attribute:
IF [Acc Type] IN (410)
THEN SUBSTR([Acc Num],4,3) = "410"AND PATTERN(SUBSTR([Acc
Num],1,3),"rich") = "d3" AND PATTERN(SUBSTR([Acc
Num],7,8),"rich") = "d8"
ELSE IF [Acc Type] IN (510)
THEN SUBSTR([Acc Num],4,3) = "510" AND PATTERN(SUBSTR([Acc
Num],1,3),"rich") = "d3"
AND PATTERN(SUBSTR([Acc Num],7,8),"rich") =
"d8"
ELSE PATTERN([Acc Num], "rich") = "d20"
|
NOT |
Reverses the truth value of the statement that follows, resulting in the
opposite of the statement it evaluates.
- Syntax
-
NOT attribute1 = value1
where attribute1 = value1 is the statement to be
reversed.
Note the following guidelines:
- NOT can also be used with the AND and OR operators.
- Expressions are evaluated from left to right. Precedence is
implied by the order shown in the expression, although
parenthetical statements always take priority.
- Statements are not case-sensitive.
- Examples
-
- The following reverses (disconnects) the value "JOHN" from
the attribute first_name: NOT(first_name="JOHN")
- The following reverses (disconnects) the values tomato and
fruit from the attributes Name and Type: NOT Name = "tomato"
AND Type = "fruit"
|
OR |
An inclusive disjunction between two statements. At least one statement
must be true for the disjunction to be true.
- Syntax
-
attribute1 = value1 OR attribute2 = value2
where
attribute1 = value1 is the first disjunctive
statement in the expression.
attribute2 = value2 is the second disjunctive statement.
Note the following guidelines:
- OR can also be used with the AND and NOT operators
- Expressions are evaluated from left to right. Precedence is
implied by the order shown in the expression, although
parenthetical statements always take priority.
- Any comparison operator can be used to join statements.
- Statements are not case-sensitive.
- Examples
-
- The following returns a correct value if either statement is
true. If neither is true, no value is returned: age > 21
OR year_of_birth > 1990
- The following shows that either the value tomato is valid
for the Name attribute or the value fruit is valid for the
Type attribute: Name = "tomato" OR Type = "fruit"
- The following expression validates that if the Sort Code
attribute is populated, then it is correct for the country in
the Client attribute: Sort Code = "" OR (PATTERN(Sort
Code,"default") LIKE "d9" AND Client LIKE "US*") OR
(PATTERN(Sort Code,"default") LIKE "d2pd2pd2" AND Client LIKE
"A*")
|
Set |
IN |
Tests whether a value is equal to a value in a list.
- Syntax
-
value IN
("value1","value2",..."valueN")
OR
value IN
LIST("value1","value2",..."valueN")
OR
value IN
"value1","value2",..."valueN"
where
-
value is the value to be tested.
-
value1,value2,valueN are the values in the set to be
tested for a match.
Note: When using the IN operator in Quality, the LIST operator is
automatically added to the syntax in the settings output file.
You do not have to add LIST to the expression.
- Examples
-
- You can create a filter to return certain values. The
statement Name IN ("tomato","pear") results in returning the
rows where tomato or pear occur in the Name attribute.
- You can create validation and business rules based on
whether a value matches one of a set of values. For example,
IF [apt_num] IN (2??) THEN… ELSE IF [apt_num] IN
(3??,4??,5??) THEN…
|
String |
LIKE |
Comparison operator that searches for a value that matches a specified
pattern.
- Syntax
-
value LIKE "pattern"
where
-
value is the attribute to be searched.
- "pattern" is the pattern used for the match.
Note the following guidelines:
- To broaden the match criteria, use the wildcard characters
asterisk (*) and question mark (?), as follows:
- (?) specifies a single character.
- (*) specifies zero or more consecutive characters in a
string.
- You can use more than one of each wildcard character
in an expression; for example, "s*@h*tm*l.*",
"*Nelson*", and "*05??2010"
- If you are searching on an asterisk or a question mark
and do not want to use it as a wildcard character, you
must escape it using a forward slash (/) as escape
character when using it for
expressions or a backward slash (\) escape character
for profiling (Discovery) expressions. For example, in
a
expression, to search on "abc*xyz" and "abc?xyz", write
the patterns as "abc/*xyz" and "abc/?xyz". To search on
a pattern that includes "/*", for example "abc/*xyz",
you write the pattern as "abc///*xyz".
- Use square brackets ([ ]) to specify any one group of
characters in a set; for example [a,b,c]. Use a dash (-) to
specify a range of characters; for example, [1-10].
- Examples
-
- If ATTR1 contains the values test and testing, the
expression ATTR1 LIKE "tes?" returns "test". To return both
"test" and "testing", use ATTR1 LIKE "tes*".
- If ATTR2 contains the values "stick" and "stack", the
expression ATTR2 LIKE "st[a-j]ck" returns both.
- The following shows how to view all values in the NAME
attribute that contain the string "cust": NAME LIKE
"*cust*".
- To find all Product identification numbers with a 5 in the
3rd position, use the following: Product_Id LIKE
"??5*".
|
|| |
Concatenates multiple arguments (such as strings, attributes, and
integers) with no space between each argument.
- Syntax
-
argument1 || argument2 || argument3
where:
-
argument1 indicates the first argument in a series of
concatenated arguments.
-
argument2 indicates the second argument in a
series.
-
argument3 indicates the third argument, and so
on.
- Examples
-
- "One" || ATX || 3 returns OneATX3.
- "Nelson" || "Ruiz" returns "NelsonRuiz".
- House No - 2 || House No - 1 results in 01 for all
records.
|
|: |
Concatenates multiple arguments (such as strings, attributes, and
integers) with a space between each argument.
- Syntax
-
argument1|: argument2 |: argument3
where
-
argument1 indicates the first argument in a series of
concatenated arguments.
-
argument2 indicates the second argument in a
series.
-
argument3 indicates the third argument, and so
on.
- Examples
-
- "One" |: ATX |: 3 returns One ATX 3.
- "Nelson |: Ruiz" returns "Nelson Ruiz".
|