Query Condition Expressions Using InetSoft's BI Software

The query condition expression grammar is based on the SQL conditions. Most of the expressions have a construction identical to their SQL counterparts. Like SQL, all reserved words are case-insensitive. However, all names, including variables and child nodes, are case-sensitive. We will cover the complete list of conditional expressions and give a few examples of advanced usage.

Named Values

In SQL’s conditions, the column values can be used in expressions for calculation or comparison. Similarly, the Data Modeler condition expressions can reference values on the data tree. However, since the hierarchical data model used by the Data Modeler supports much richer organization of data, the expressions support a few more types of data references.

Child Node Values

The selection node and all of its child nodes can be referenced by the conditional expression. The value of the selection node can be referenced using the reserved word, ‘this’.

this is null

Its immediate child nodes can be referenced by their names,

salary > 50000

The descendents of the child nodes can be referenced with a node path. A node path is constructed by appending the node names from the child node, and all of the nodes leading to the referenced node, with the node names separated by dots. For example, the child of ‘location’ node can be referenced using:

location.city = 'New York'

Using the same algorithm, a path can be constructed for arbitrary levels down the sub-tree,

child1.grandchild2.grandgrandchild3 like 'N%'

View a 2-minute demonstration of InetSoft's easy, agile, and robust BI software.

If the referenced node can have multiple instances, the node value is extracted from the first instance of the node. For example, the ‘Skill’ node in the ‘Employee’ tree can have more than one instance. An employee can have multiple skills. A ‘Skill’ node describes each. If the skill child is used in an expression, it refers to the first occurrence of the ‘skill’ node.

skill like 'Java%'

It is possible to specify the exact instance by adding an index parameter to the node name.

skill like 'Java%'

The index is zero based. If the specified instance does not exist, the node reference returns a null value.

Node names used directly in an expression must not contain any spaces or other special characters. Like identifiers in a programming language, a node name must start with an alphabetic character or ‘_’, followed by zero or more alphanumeric characters. In case a node name does not conform to this requirement, or the name collides with a reserved word, the node can be referenced using the ‘node’ function,

node('first name') = 'John'

This is functionally equivalent to the direct reference to a node, but allows any node name to be used regardless of the characters in the name.

Node Attributes

Each node may have zero or more attributes. The attributes are not child nodes of the node. Each attribute contains a scalar string value. The attribute of a node can be referenced using the attribute name appended to an ‘@’ sign.

@id = '339-928-9877'

When the attribute name is used without any qualification, as in the above example, it refers to the attribute in the current node. Appending the attribute name to the node path can reference the attribute values of any child node:

child1.grandchild.@type = 'integer'

The ‘node’ function can also be used to reference an attribute:

node('child1.second child.@name') = 'John'

Variables

A variable can be added to an expression where a value is expected. The variable does not need to be declared. Whenever a variable notation is encountered, the variable is implicitly declared. If a variable is used without any further definition, it defaults to the string type, and the end user enters its value when the query is executed.

location.state = $(state)

The name of the variable can be quoted in the parentheses. This allows reserved words to be used as the variable name.

price = $('max')

If the quote were not used, the expression parser would generate an error because the word ‘max’ is used for the aggregate function and is not recognized as a regular variable name.

All variable references to the same name in all expressions used in a query are considered to be the same variable. Variables are never shared across queries, regardless of their names.

A variable can be formally defined in a query. The variable definition sets the follow variable attributes:

• Alias Variable label used when prompting end users for the variable value.

• Type Variable data type. The type determines the component used for entering the variable value and the type of the entered value. For example, if a variable is defined as a date type, a date combo box is used to enter the variable value and the entered value is a java.util.Date object.

• Value Variable default value.

A variable can also be defined as a query-based variable. In this case, the value of the variable is derived from the result of another query and the end user is not prompted for the value.

Constant Values

There are four basic types of constants: string, number, Boolean, and date.

String

A string value is a quoted character sequence. Both single quotes and double quotes are accepted.

city = 'New York' or city = "New York"

If a single or double quote is part of the string value, it needs to be escaped if the value is quoted using the same character. To escape a quote character, repeat the character twice, e.g.,

'Seven O''Clock'

Number

A number can be either an integer or a double.

age > 55

A double number can be entered using either decimal notation, or scientific notation,

ratio < 2.5

Numeric comparison is always done in double values. Therefore, an integer 50 does not equal a double 50.1. All arithmetic computation is also done in doubles. All values are converted to a double before the computation is performed. A division of two integer numbers results in a double value. No rounding is done regardless of the type of the operands.

Boolean

A Boolean constant is specified using ‘true’ or ‘false’. The case is insignificant.

instock = true

Date

There is no date constant in an expression. Instead, converting a text representation of a date to a date object can create a date value. The conversion is done using the to_date() function.

birthday > to_date("1960-12-31")

The default date format is the same as SQL, yyyy-MM-dd. An alternative date format can be supplied in the second argument of the function call:

birthday > to_date("12/31/60 12:00:00","MM/dd/yy HH:mm:ss")

The format should conform to the format defined in the java.text.SimpleDateFormat class.

Previous: List Comparison for Queries using Meta Data

Next: Simple Expressions, SQL Predicates