Page History

...

Category	Function	Description
Logic	AND	Returns TRUE if all of its arguments are TRUE
OR	Returns TRUE if any argument is TRUE
TRUE	Returns the boolean value TRUE
FALSE	Returns the boolean value FALSE
NOT	Reverses the boolean value of its argument
UNKNOWN	Returns the logical value unknown
Text	CONCATENATE	Joins several text items into one text item
JOIN	Joins several text items into one text item, while placing a separator character between them
MATCH	Checks if a string completely matches a regular expression
FIND	Returns found characters if a pattern is found within text value
FIRST	Returns the first position where one text value is found within another
LAST	Returns the last position where one text value is found within another
Statistics	AVERAGE	Returns the average of its arguments
MIN	Returns the minimum of a specified value in a list or collection
MAX	Returns the maximum of a specified value in a list or collection
COUNT	Counts the number of instances in a specified collection
Date and Time	DATETIME	Converts a date in the form of text or a date object to a dateTime object
DATE	Converts a date in the form of text or a dateTime object to a date object
ADD PERIOD TO DATE	Adds a specified period (day, month etc.) to a date
ADD PERIOD TO DATETIME	Adds a specified period (day, month etc.) to a date
SUBTRACT PERIOD FROM DATE	Subtracts a specified period (day, month etc.) from a date and time
SUBTRACT PERIOD FROM DATETIME	Subtracts a specified period (day, month etc.) from a date and time
PERIOD BETWEEN	Returns the period (days, months etc.) between two dates, two dateTimes or a date-dateTime combination
YEAR	Returns the number of the year
MONTH	Returns the number of the month
DAY	Returns the number of the day in the month
HOUR	Returns the number of hours in a dateTime object
MINUTE	Returns the number of minutes in a dateTime object
SECOND	Returns the number of seconds in a dateTime object
TODAY	Returns today’s date
NOW	Returns the current date and time
Collection	SIZE collection	Returns the size of a collection
UNIQUE collection	Filters duplicate instances in a collection
SUBSET OF	Returns a subset of a collection
UNION	Combines two collections into one new collection
INTERSECTION	Returns the intersection of two collections
Math	ADD	Adds two numbers
SUM	Sums the specified values in a collection
SUBTRACT	Subtracts two numbers
DIVIDE	Divides two numbers
DIV	Returns the integer portion of a division
MOD	Returns the remainder from division
ROUND	Rounds a number to a specified number of digits
ROUND TO MULTIPLE	Rounds a number to the nearest integer or to the nearest multiple of significance
MULTIPLY	Multiplies two numbers
PRODUCT	Multiplies the specified values in a collection
POWER	Returns the result of a number raised to a power
LESS	Compares two number values and returns TRUE when one is smaller than the other
LESS OR EQUAL	Compares two number values and returns TRUE when one is equal or smaller than the other
GREATER	Compares two number values and returns TRUE when one is greater than the other
GREATER OR EQUAL	Compares two number values and returns TRUE when one is equal to or greater than the other
EQUAL	Compares two values and returns TRUE when they are equal
NOT EQUAL	Compares two number values and returns TRUE when they are not equal
List	EXISTS	Returns TRUE if there is an instance that matches the specifications
ALL	Returns a collection of all instances of a specified entity
COLLECT FROM	Returns a collection for a specified entity
COLLECT FROM WHERE	Returns a collection for a specified entity that matches the specified conditions
COLLECT FROM NAMED	Returns a collection for a specified entity
COLLECT FROM NAMED WHERE	Returns a collection for a specified entity that matches the specified conditions
UNPACK	Returns the content of a single-valued list
Conversion	TEXT	Converts a value type to string
INTEGER	Converts a value type to integer
NUMBER	Converts a value type to number
CURRENCY	Converts a value type to currency
PERCENTAGE	Converts a value type to percentage
DATE	Converts a value type to date
DATETIME	Converts a value type to datetime
BOOLEAN	Converts a value type to boolean
More functions	ISUNKNOWN	Replaces an unknown value with a specified value
IS A
TYPE OF
AUTHORIZATIONROLES
CHANNEL
LANGUAGE
PAGE
PRIVILEGES
EVENT

Collection

SIZE collection

This function determines the size of a collection.

Syntax

SIZE(collection)

collection - A collection of attribute or entity instances.

Return type

integer

Example

Suppose you created a Parent and a Child entity. Parent has a multivalued relation with Child via the relation Parent.has_Children.

If the following instances where created:

...

Then:

without selecting a parent SIZE(Parent.has_children) results in an error
for Parent_1 SIZE(Parent.has_children) results in 3
for Parent_2 SIZE(Parent.has_children) results in 1
for Child_1 SIZE(Child.hobbies) results in 2
for Child_3 SIZE(Child.hobbies) results in 3
for Child_4 SIZE(Child.hobbies) results in 1
SIZE(? (= UNKNOWN)) results in 0¹⁾

¹⁾ COUNT and SIZE are similar except for ?: COUNT(?) = UNKNOWN

UNIQUE collection

The UNIQUE function filters duplicate items from a collection. An expression resulting in a collection, never contains duplicate values. A subexpression however, can contain duplicates.

Syntax

UNIQUE(collection)

collection - A collection of attribute or entity instances.

Examples

Suppose the following model.

...

COLLECT Person.name FROM ALL Person results in “Kim”, “Rick”, “Bob” (a result never contains duplicate values)
SIZE(COLLECT Person.name FROM ALL Person) results in 4 (a subexpression can contain duplicate values)
SIZE(UNIQUE(COLLECT Person.name FROM ALL Person)) results in 3 (the collection holds three unique values)

SUBSET OF

This function returns TRUE if the items in a collection are all present in another collection.

Syntax

collection1 SUBSET OF collection2

collection1 - The collection that is tested to be a subset of the second collection.
collection2 - The collection that is tested to hold all the items in the first collection.

Return type

boolean

Examples

('a', 'b', 'c') SUBSET OF ('a', 'b', 'c', 'd') results in TRUE
('a', 'b', 'c', 'd') SUBSET OF ('a', 'b', 'c') results in FALSE
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) results in TRUE if Person.hobbies = “Tennis”, “Soccer”
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) results in UNKNOWN if Person.hobbies = ?
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, ?]) results in FALSE if Person.hobbies = “Tennis”, “Soccer” ¹⁾
('a', 'b', 'c') SUBSET OF (['a', 'b', 'c']) will result in an error.²⁾

¹⁾ This one is odd, but is a result of backward compatibility.

²⁾ Values between single quotes are considered value list items. For backwards compatibility reasons, a comma separated sequence of value list items is treated as a collection. That's why there is no need to enclose the values between square brackets. In fact if you do at the square brackets you create a matrix rather than a list.

UNION

Adds two collections of the same base type to a new collection.

Syntax

UNION(collection1, collection2)

collection1 - First collection to be added to the new collection.
collection2 - Second collection to be added to the new collection.

Return type

collection

Examples

Suppose the following model. Entity Parent has a multivalued relation with entity Child via the relation Parent.has_Children.

...

UNION(Parent[Parent_1].has_Children, Parent[Parent_2].Has_Children) results in a collection of all child instances
UNION(Parent[Parent_1].has_Children.name, Parent[Parent_2].Has_Children.name) results in “Kim”, “Rick”, “Bob”, “Mary”
after selecting Child_2, UNION(Child.hobbies, “Reading”) results in “Tennis”, “Dancing”, “Reading”
UNION(Child[Child_1].hobbies, Child[Child_2].hobbies) results in “Reading”, “Dancing”, “Tennis”

INTERSECTION

This function determines the intersection of two collections. It returns a collection containing the items that are present in both specified collections.

Syntax

INTERSECTION(collection1, collection2)

collection1, collection2 - Collections to be intersected. These collections must be of the same base type.

Return type

collection

Examples

Suppose the following model. Entity Teacher has a multivalued relation with entity Child via the relation Teacher.teaches_Children.

...

INTERSECTION(Teacher[Teacher_1].teaches_Children, Teacher[Teacher_2].teaches_Children) results in a collection of child instances Child_1 and Child_3
INTERSECTION(Teacher[Teacher_1].teaches_Children.name, Teacher[Teacher_2].teaches_Children.name) results in a “Kim”, “Bob”
INTERSECTION(Child[Child_1].hobbies, Child[Child_3].hobbies) results in “Reading”
INTERSECTION(Child[Child_2].hobbies, Child[Child_3].hobbies) results in an empty list

Math

ADD

Use the '+' (plus) sign to add values of some type of number.

Syntax

number1 + number2 + ...

number1 - First number or attribute of some type of number to add.
number2 - Second number or attribute of some type of number to add.

Return types

number + number = number
integer + integer = integer
currency + currency = currency
percentage + percentage = percentage
number + integer = number
number + currency = currency
number + percentage = percentage
integer + currency = currency
integer + percentage = percentage

Examples

Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.

Child.pocketMoney + 3.00 = 5.50 of type currency
Child.pocketMoney + 3 = 5.50 of type currency
2.50 + 3 = 5.5000 of type number
100 + Bonus.standard = 105.00 of type percentage
Child.pocketMoney + Bonus.standard results in an error

...

Space shortcuts

Page tree

Versions Compared

Old Version 6

New Version 7

Key

Collection

SIZE collection

UNIQUE collection

SUBSET OF

UNION

INTERSECTION

Math

ADD