Syntax for the free form editor

Create data rule definitions by using appropriate syntax in the free form editor.

Purpose

Use the syntax below to build data rule definitions with the free form editor.

Parameters

The following table provides the syntax to use when you are building rule logic with the free form editor:

Table 1. Syntax to use when working with the free form editor
Concept	Syntax	Example
Boolean keywords	AND, OR (the syntax is not case sensitive)	value >0 AND value<100
Boolean grouping	( )	(valueA=0) and (valueB=0 or valueC=0)
Conditional keywords	IF, THEN, ELSE (the syntax is not case sensitive)	IF age<18 THEN status='child'
Variables	Variable names can be made up of letters, unicode characters belonging to the Letter category, digits, ‘_' (underscores), '?' (question marks), or ‘$'(dollar signs). The first character must be a letter. Periods or spaces are not allowed.	age>0 AND client_ID exists
Numeric constants	Numeric constants must consist of the numeric characters 0-9. No quotation marks, decimal separators, or fraction separators are allowed. Numeric constants are in the machine format (12345.789) and are not in the locale specific format.	col>10 and col<12343.35
String constants	All string constants must have single quotations around them when declared, for example 'value'. If the value itself contains single quotes, you must put a backslash before the quotations. The backslash itself is escaped with a double backslash.	value<>'xyz' AND value<>'John\'s idea' AND col<>'C:\\docs'
Date or time constants	Functions dateValue(), timeValue(), and timestampValue() must be use to hard code a time, date, or timestamp in an expression.	time>timeValue('08:45:00') , date>dateValue('2008-31-12'), timestamp>timestampValue ('2008-31-12 08:45:00')
Lists	You can specify a comma separated list of values within a {} (braces) group.	{'a','b','c'} {1.54, 2.32, 34.12}
Arithmetic operators	+ (plus sign for addition), / (forward slash for division), - (minus sign for subtraction) * (asterisk for multiplication. Use parentheses to group arithmetic operations.	(valueA+valueB)/2 = (valueC-valueD)*(valueE)
Scalar function	Specify the function name, and then the parameters for that function in parenthesis, for example, "function_name(parameter1, parameter2)." Supported functions: date (), datevalue (string,format), day (date), month(date), weekday(date), year(date), time(), timevalue(string,format), timestampvalue (value,format), timestamp(), hours(time), minute(time), seconds(time), datediff(date1, date2), timediff (time1, time2), abs(value), avg(value), exp(value), max(value), min(value), sqrt(value), standardize(col,group), stddev(col, group), sum(value), coalesce (value, nullValueReplacement), lookup (value, keyRefCol, valRefCol), ascii(char), char(asciiCode), convert(originalString, searchFor, replaceWith), count(column), lcase(string), index(string, substring), left(string, n), len(string), ltrim(string), pad(string, begin, end), lpad(string, n), rpad(string, n), right(string, n), rtrim(string), substring(string, begin, length), str(string, n), tostring(value, format string), trim(string), ucase(string), val(value),	ltrim(upper(col))='ABC'
Comments	# (number sign) comment, \\(double backslash) comment or \* (back slash and an asterisk) comment *\ (asterisk and a back slash)	# Test the value below the limit col<$limit //default=100 or \* end of test *\

Data check types

You can use the following types of data checks with the free form editor:

>

Checks to see if your source value is greater than your reference data.

>=

Checks to see if your source value is greater than or equal to your reference data.

<

Checks to see if your source value is less than your reference data.

<=

Checks to see if your source value is less than or equal to your reference data.

contains

Checks your data to see if contains part of a string. The check returns true if the value represented by the reference data is contained by the value represented by the source data. Both source and reference data must be of string type.

exists

Checks your data for null values. The check returns true if the source data is not null, and false if it is null.

=

Checks to see if the source value equals the reference data.

in_reference_column

Checks to determine if the source data value exists in the reference data column. For this check, the source data is checked against every record of the reference column to see if there is at least one occurrence of the source data.

in_reference_list

Checks to determine if the source data is in a list of references, for example, {'a','b','c'}. The list of values are entered between brackets ({ }) and separated by commas. String values can be entered by using quotations and numeric values should be in the machine format (123456.78). A list can contain scalar functions.

is_date

Checks to determine if the source data represents a valid date. For this type of check you cannot enter reference data.

is_numeric

Checks to determine if the source data represents a number. For this type of check you cannot enter reference data.

matches_format

Checks to make sure your data matches the format that you define, for example:

IF country='France' then phone matches_format '99.99.99.99.99'

Both source and reference data must be strings.

matches_regex

Checks to see if your data matches a regular expression, for example:

 postal_code matches_regex '^[0-9]{5}$'

Both source and reference data must be strings.

occurs

Checks to evaluate if the source value occurs as many times as specified in the reference data in the source column. The reference data for this check must be numeric. For example, if in the firstname column, "John" appears 50 times and the rule logic is written as firstname occurs <100, after you bind the column firstname with the literal John, then records with "John" in the firstname column meet the conditions of the rule logic. You have the following occurrence check options:

occurs>=
occurs>
occurs<=
occurs<

unique

Checks to evaluate if the source data value occurs only one time (is a cardinal value) in the source data.