Expression Builder Operators

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.

■  The following associates the values tomato and fruit to attributes Name and Type: Name = "tomato" AND Type = "fruit"

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".

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 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".

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 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.

■  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"

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.

■  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*")

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.

■  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… 

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 TSQ expressions or a backward slash (\) escape character for profiling (Discovery) expressions. For example, in a TSQ 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].

■  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*". 

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.

■  "One" || ATX || 3 returns OneATX3.

■  "Nelson" || "Ruiz" returns "NelsonRuiz".

■  House No - 2 || House No - 1 results in 01 for all records.