| #1 Ranking: Read how InetSoft was rated #1 for user adoption in G2's user survey-based index |
|
Read More |
Arithmetic Expressions
Numeric computations are always done using double values. The following operators are supported:
Comparison Expressions
The expression ‘is null’ Comparisons can be done between any two values. If the two values have the same type, the comparison is done according to the type. If two values are of different types, then both values are converted to strings and compared using text comparison.
Logical Expressions
The conditional expressions are all short-circuit logic operations. In the ‘and’ expression, the right-hand operand is only evaluated if the left-hand operand is true. In the ‘or’ expression, the right-hand operand is only evaluated if the left-hand operand is false.
The operands of logic expressions can be any type. If an operand is not a 125 of 137 Boolean value, it is converted to a Boolean. If the value is null, it is converted to a false value. Otherwise, it is converted to a true value.
 |
“We evaluated many reporting vendors and were most impressed at the speed with which the proof of concept could be developed. We found InetSoft to be the best option to meet our business requirements and integrate with our own technology.”
- John White, Senior Director, Information Technology at Livingston International |
SQL Predicates
The query condition also supports the other predicate expressions in defined SQL.
Between Operator
The ‘between’ comparison is shorthand for a ‘greater than or equal to’ and a ‘less than or equal to’ expression.
salary between 30000 and 40000
This is equivalent to the following condition.
salary >= 30000 and salary <= 40000
A negative ‘between’ comparison can be created using ‘not between’.
salary not between 30000 and 40000
In Operator
The ‘in’ operator tests if a value is one of the values in a given list. In the simplest form, the value list is given directly in the condition. Each value in the list can be either a constant or an expression that returns a scalar value.
state in ('NY', 'NJ')
In SQL, the list can be the result of a sub-query. This is also supported in the Data Modeler queries. However, the sub-query has a different syntax. The details for using sub-queries are discussed later.
state in (query('tristates'))
In addition to supporting sub-queries in the ‘in’ operation, the list value can come from any named value, such as a variable or a sub-selection. Like the ‘between’ operator, there is a shorthand for adding a logic negation to the condition.
state not in ($(tristates_variable))
SQL Pattern Matching Operator
SQL has a very simple pattern-matching operator. The ‘like’ operator supports two pattern characters:
% = match any character sequence
? = match any single character
company like 'I%'
This condition matches any string starting with an ‘I’ character.
skill like 'C??'
This condition matches any string starting with a ‘C’ character, followed by two arbitrary characters. Similar to the ‘in’ operator, a logic negation can be added with a short hand:
skill not like 'C%'
Existence Operator
In SQL, the existence test is applied to a sub-query. The condition tests if any result is returned by the sub-query. In a Data Modeler query condition, a sub-query is treated the same as any named value. Therefore the existence test can be applied to any named value.
The result of the condition depends on the type of the data in the named value. If the value is a table, the existence test checks for the existence of any rows in the table. If the table is empty, the test returns false. Otherwise, it returns true.
If the value is a sequence (a node holding the collection of child nodes with the same type), the test checks for the existence of any child nodes in the sequence. If the sequence is empty, the test returns false. Otherwise, it returns true.
For all other values, the test is true if the value is not null.
exists query('salary over 200000')
The existence test can also be applied to a node. For example, to check if the “link” node exists:
exists link
List Comparison
A value can be compared to a list of values. The result of the comparison depends on what type of comparison is declared. Two list comparison types are supported:
• Any The comparison is successful if the value compares to true with any one of the values on the list.
• All The comparison is successful if and only if the value compares to true with all values on the list.
The following comparison tests if the price is greater or equal to all prices in the group:
price >= all query('all prices')
The ‘query’ function executes a sub-query and returns the results.
The next condition tests if the state is in one of the states in the list:
state = any query('tristates')
Regular Expressions
The regular expression is one of the most powerful pattern matching mechanisms. It is used extensively in text processing, such as using Perl and AWK. Regular expressions are supported in the expressions for matching a string value against a pattern. The pattern matching can be done on one string value or on a list of values.
Regular expression operations are relatively expensive compared toregular string comparisons. Do not use them unless the conditionsrequire it.
Regular Expression Operator
The Data Modeler query condition adds support for regular expression pattern matching. Regular expression is a much more powerful pattern language than the simple patterns supported by the ‘like’ operator.
The regular expression supported by the Data Modeler conforms to Perl5 regular expression syntax. For more details on the regular expression, please consult the Perl5 documentation.
Language Summary. The following is a summarization of the regular expression syntax.
Alternatives separated by |
Quantified atoms
{n,m} Match at least n but not more than m times.
{n,} Match at least n times.
{n} Match exactly n times.
* Match 0 or more times.
+ Match 1 or more times.
? Match 0 or 1 times.
a . matches everything except \n
a ^ is a null token matching the beginning of a string orline (i.e., the position right after a newline or right before the beginning of a string)
a $ is a null token matching the end of a string or line (i.e., the position right before a newline or right afterthe end of a string)
Character classes (e.g., [abcd]) and ranges (e.g., [a-z])
Special backslashed characters work within a character class(except for backreferences and boundaries).
\b is backspace inside a character class
Special backslashed characters
\b null token matching a word boundary (\w on one side and \W on the other)
\B null token matching a boundary that isn't a word boundary
\A Match only at beginning of string
\Z Match only at end of string (or before newline at the end)
\n newline
\r carriage return
\t tab
\f formfeed
\d digit [0-9]
\D non-digit [^0-9]
\w word character [0-9a-z_A-Z]
\W a non-word character [^0-9a-z_A-Z]
\s a whitespace character [ \t\n\r\f]
\S a non-whitespace character [^\t\n\r\f]
\xnn hexadecimal representation of character
\cD matches the corresponding control character
\nn or \nnn octal representation of character unless a backreference.
a \1, \2, \3, etc. match whatever the first, second, third,etc. parenthesized group matched. This is called abackreference. If there is no corresponding group, thenumber is interpreted as an octal representation of a character.
\0 matches null character
Any other backslashed character matches itself Expressions within parentheses are matched as subpattern groups and saved for use by certain methods.
List Regular Expression Matching
The ‘match’ operator matches a string value with a regular expression.
company match 'I.*'
Similar to the ‘like’ operator, a logic negation can be added with a ‘shorthand’ method.
company not match 'I.*'
Like the list comparison expression, a list of values can be matched against a regular expression. The comparison can be defined as either ‘Any’ or ‘All’.
any skill match 'C.*Programming'
For the ‘Any’ type list matching, the result is true if any value in the list matches the regular expression. For an ‘All’ type list matching, the result is only true if all values in the list match the regular expression.
Aggregate Functions
Aggregate functions calculate values based on a list of values. The function can be applied to any value that contains a list, including sub-queries and sub-selections. In all functions, if the list value is null, it is treated as an empty list.
Sum
The Sum function calculates the total of the values in a list.
revenue > sum(query('ne-sales'))
Like all computational expressions, the values in the list are all converted to doubles to calculate the total. If any value on the list is not a number or a string convertible to a number, the value is converted to a zero.
Avg
The Avg function calculates the average of the values in a list.
price > avg(filter('product.price'))
The average is calculated as a double value. All items on the list, regardless of whether they are valid numbers, are counted into the average. All non¬numeric values are treated as zero.
Min
The Min function returns the minimum value on a list.
price = min(filter('product.price'))
Max
The Max function returns the maximum value on a list.
price = max(filter('product.price'))
Count
The Count function returns the number of items on a list. count(query('PDAs')) > 10