Page History

...

This function determines whether an instance of a specified entity exists, optionally meeting certain criteria.

Syntax

Code Block
EXISTS instances [WHERE condition]

instances - Instances to search.
condition - boolean expression that represents the criterion the instance has to meet.

Return type

boolean

Examples

Suppose the following data model.

Person.name	Person.gender	Person.age
“Kim”	“f”	23
“Rick”	“m”	35
“Bob”	“m”	42
“John”	“m”	19
“Mary”	“f”	33

EXISTS Person = TRUE
EXISTS Person WHERE (Person.age < 18) = FALSE
EXISTS Person WHERE (Person.gender = “m” AND Person.age > 35) = TRUE

UI Text Box

type	note

The return value of a boolean can be TRUE, FALSE or UNKNOWN.

Include Page

	_nav_BackToTop
	_nav_BackToTop

EACH

This function determines whether all instances of a specified entity meet a certain criteria

Syntax

Code Block
EACH instances WHERE condition

instances - A collection of instances to search.
condition - Boolean expression that represents the criterion the instance has to meet.

Return type

boolean

Examples

Suppose the following data model.

Person.name	Person.gender	Person.age
“Kim”	“f”	23
“Rick”	“m”	35
“Bob”	“m”	42
“John”	“m”	19
“Mary”	“f”	33

EACH Person WHERE (Person.age < 18) = FALSE
EACH Person WHERE (Person.age > 18) = TRUE
EACH Person WHERE (Person.age < 20) = FALSE
EACH Person WHERE (Person.gender = “m” OR Person.age > 35) = FALSE
EACH Person WHERE (Person.gender = “m” OR Person.age > 20) = TRUE

Include Page

	_nav_BackToTop
	_nav_BackToTop

ALL

Use this function to create a collection of all instances of a specified entity.

Syntax

Code Block
ALL entity

entity - Entity of which to collect all its instances.

Return type

collection of entity instances

Examples

Suppose the following data model. The Parent and Child entities both have Person as base entity.

Parent instance	Child instance
Parent_1	Child_1
Parent_1	Child_2
Parent_1	Child_3
Parent_2	Child_4

ALL Parent = [ Parent_1 , Parent_2 ]
ALL Child = [ Child_1 , Child_2 , Child_3 , Child_4 ]
ALL Person = [ Parent_1 , Parent_2 , Child_1 , Child_2 , Child_3 , Child_4 ]

Include Page

	_nav_BackToTop
	_nav_BackToTop

COLLECT FROM [WHERE]

Use this function to create a collection of entity or attribute instances (meeting certain criteria).

Syntax

Code Block
COLLECT entity \| attribute FROM collection [WHERE expression]

entity or attribute - Entity or attribute to collect.
collection - A collection of entity instances.
expression - Boolean expression that represents the criterion the instance has to meet.

Return type

collection of entity instances
collection of attribute values

Examples

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

Teacher instance	Child instance	Child.name	Child.hobbies
Teacher_1	Child_1	“Kim”	“Reading”, “Dancing”
Teacher_1	Child_2	“Rick”	“Tennis”, “Dancing”
Teacher_1	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Teacher_2	Child_1	“Kim”	“Reading”, “Dancing”
Teacher_2	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Teacher_2	Child_4	“Mary”	“Football”

COLLECT Child.name FROM ALL Child results in a collection of the values “Kim”, “Rick”, “Bob” and “Mary”
COLLECT Child FROM Teacher[Teacher_2].teaches_Children results in a collection of the instances Child_1, Child_3 and Child_4
COLLECT Child.hobbies FROM Teacher[Teacher_1].teaches_Children results in a collection of the values “Reading”, “Dancing”, “Tennis”, “Painting” and “Basketball”
COLLECT Child.name FROM ALL Child WHERE (Child.hobbies = “Reading”) results in a collection of the values “Kim” and “Bob”
COLLECT Child.hobbies FROM ALL Child WHERE (Child.name = “Mary”) results in a collection of the value “Football”

Include Page

	_nav_BackToTop
	_nav_BackToTop

COLLECT FROM NAMED [WHERE]

Use this version of the COLLECT FROM [WHERE] function for complex nested selections in which it is necessary to use an alias.

Syntax

Code Block
COLLECT entity \| attribute FROM collection NAMED alias [WHERE expression]

entity or attribute - Entity or attribute to collect. This should contain the alias, e.g. alias.Name.
collection - A collection of entity instances.
alias - A name for the collection.
expression - Boolean expression that represents the criterion the instance has to meet.

Return type

collection of entity instances
collection of attribute values

Examples

Suppose the following model with only instances of entity Person. Person.hasChildren is a relation from Person to Person.

Person instance	Person.Name	Person.Age	Person.hasChildren
Person_1	“Kim”	16
Person_2	“Rick”	38	Person_3, Person_5
Person_3	“Bob”	8
Person_4	“Julia”	42	Person_1
Person_5	“Sam”	3
Person_6	“Joan”	34	Person_3, Person_5

To retrieve all the parent names, we use this expression:

Code Block
COLLECT Parent.name FROM COLLECT Person FROM ALL Person WHERE (Person.hasChildren != ?) NAMED Parent

This expression results in “Rick”, “Julia” and “Joan”.

To retrieve all children names, we use this expression:

Code Block
COLLECT Child FROM ( COLLECT Person.hasChildren FROM ALL Person WHERE (Person.hasChildren != ?)) NAMED Child

This expression results in a collection of three instances of Person (i.e Person_1, Person_3 and Person_5, with names Kim, Bob and Sam).

To retrieve all children younger than 15, we use this expression:

Code Block
COLLECT Child FROM ( COLLECT Person.hasChildren FROM ALL Person WHERE (Person.hasChildren != ?)) NAMED Child WHERE (Child.Age < 15)

This expression results in a collection of two instances of Person (i.e Person_3 and Person_5, with names Bob and Sam).

To retrieve the children’s names with a parent older than 40 years:

Code Block
COLLECT Child.Name FROM ( COLLECT Person.hasChildren FROM ALL Person WHERE (Person.hasChildren != ? AND Person.Age > 40)) NAMED Child

This expression results in a collection with only the name “Kim”.

You can make it as complex as you like, for example:

Code Block
COLLECT Child.Name FROM ( COLLECT Parent.hasChildren FROM ( COLLECT Person FROM ALL Person WHERE (Person.hasChildren != ?)) NAMED Parent WHERE (Parent.Age > 40 ) ) NAMED Child WHERE (Child.Age < 18)

This expression results also in a collection with only the name “Kim”.

UI Text Box

type	note

In some occasions, a local variable could be a good alternative for using an alias. E.g. if you want a collection of all persons except the person that is currently active, this would be simple and transparent:

Code Block
thisPerson := Person ; COLLECT Person FROM ALL Person WHERE (Person != thisPerson)

UNPACK

Use this function to extract the value from a single valued collection or list.

Syntax

Code Block
UNPACK collection/list

collection/list - A collection or list of one entity or attribute instance.

Return type

entity instance
attribute value of any type

Examples

Suppose the following data model.

Person.name	Person.SequenceNumber
“Bob”	654
“Jane”	523
“Mary”	667
“Rick”	500
“Ron”	490
“Jenny”	765

UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Ron”
In case a second entry "Ron","490" exists, the expression "UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person)))" will fail, because the UNPACK cannot resolve a list with two elements. To solve this the UNIQUE function has to be used: "UNPACK(UNIQUE(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MIN(COLLECT Person.SequenceNumber FROM ALL Person))))", which will result in "Ron".
UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MAX(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Jenny”

Include Page

	_nav_BackToTop
	_nav_BackToTop

SIZE

This function determines the size of a collection.

Syntax

Code Block
SIZE ( collection )

collection - A collection of attribute or entity instances. This can be an expression or a relation attribute for instance.

Return type

integer

Example

Suppose you have a Parent and a Child entity, where Parent has a multivalued relation with Child via the relation Parent.has_Children. With this model the following instances are created:

Parent instance	Child instance	Child.name	Child.hobbies
Parent_1	Child_1	“Kim”	“Reading”, “Dancing”
Parent_1	Child_2	“Rick”	“Tennis”, “Dancing”
Parent_1	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Parent_2	Child_4	“Mary”	“Football”

Then:

if Parent_1 is active, SIZE ( Parent.has_Children ) = 3
if Parent_2 is active, SIZE ( Parent.has_Children ) = 1
without an active Parent instance, SIZE ( Parent.has_Children ) results in an error
if Child_1 is active, SIZE ( Child.hobbies ) = 2
if Child_3 is active, SIZE ( Child.hobbies ) = 3
if Child_4 is active, SIZE ( Child.hobbies ) = 1
SIZE ( ? ) = 0

UI Text Box

type	note

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

UNIQUE

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

Code Block
UNIQUE ( collection )

collection - A collection of attribute or entity instances.

Examples

Suppose the following model.

Person instance	Person.name
Person_1	“Kim”
Person_2	“Rick”
Person_3	“Bob”
Person_4	“Rick”

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

SUBSET OF

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

Syntax

Code Block
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') = TRUE
('a', 'b', 'c', 'd') SUBSET OF ('a', 'b', 'c') = FALSE
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) = TRUE if Person.hobbies = “Tennis”, “Soccer”
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, “Music”]) = UNKNOWN if Person.hobbies = ?
(Person.hobbies) SUBSET OF ([“Tennis”, “Soccer”, ?]) = FALSE if Person.hobbies = “Tennis”, “Soccer”
('a', 'b', 'c') SUBSET OF (['a', 'b', 'c']) will result in an error.

UI Text Box

type	note

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 add the square brackets you create a matrix rather than a list.

Include Page

	_nav_BackToTop
	_nav_BackToTop

UNION

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

Syntax

Code Block
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 you have a Parent and a Child entity, where Parent has a multivalued relation with Child via the relation Parent.has_Children. With this model the following instances are created:

Parent instance	Child instance	Child.name	Child.hobbies
Parent_1	Child_1	“Kim”	“Reading”, “Dancing”
Parent_1	Child_2	“Rick”	“Tennis”, “Dancing”
Parent_1	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Parent_2	Child_4	“Mary”	“Football”

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 ) = “Kim”, “Rick”, “Bob”, “Mary”
if Child_2 is active, UNION ( Child.hobbies , “Reading” ) = “Tennis”, “Dancing”, “Reading”
UNION ( Child[Child_1].hobbies , Child[Child_2].hobbies ) = “Reading”, “Dancing”, “Tennis”

Include Page

	_nav_BackToTop
	_nav_BackToTop

INTERSECTION

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

Syntax

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

Teacher instance	Child instance	Child.name	Child.hobbies
Teacher_1	Child_1	“Kim”	“Reading”, “Dancing”
Teacher_1	Child_2	“Rick”	“Tennis”, “Dancing”
Teacher_1	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Teacher_2	Child_1	“Kim”	“Reading”, “Dancing”
Teacher_2	Child_3	“Bob”	“Painting”, “Basketball”, “Reading”
Teacher_2	Child_4	“Mary”	“Football”

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 ) = “Kim”, “Bob”
INTERSECTION ( Child[Child_1].hobbies , Child[Child_3].hobbies ) = “Reading”
INTERSECTION ( Child[Child_2].hobbies , Child[Child_3].hobbies ) results in an empty list

Include Page

	_nav_BackToTop
	_nav_BackToTop

DIFFERENCE

This function returns a collection containing all the items from collection1 that are not present in collection 2.

Syntax

Code Block
DIFFERENCE ( collection1 , collection2 )

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

Return type

collection

Examples

DIFFERENCE ( [ "a", "b", "c"] , [ "c", "d", "e" ] ) = [ "a" , "b" ]
DIFFERENCE ( [ "nv" , "bv" ] , [ "NV" ] ) = [ "bv" ]
DIFFERENCE ( 1 , 1 ) results in an empty list

Include Page

	_nav_BackToTop
	_nav_BackToTop

SYMMETRIC_DIFFERENCE

This function determines the symmetric difference between two collections. It returns a collection with the elements of the provided collections which are in either one of the collections, but not in both.

Syntax

Code Block
SYMMETRIC_DIFFERENCE ( collection1 , collection2 )

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

Return type

collection

Examples

SYMMETRIC_DIFFERENCE ( [ "a", "b", "c"] , [ "c", "d", "e" ] ) = [ "a", "b", "d", "e" ]
SYMMETRIC_DIFFERENCE ( [ "nv" , "bv" ] , [ "NV" ] ) = [ "bv" ]
SYMMETRIC_DIFFERENCE ( 1 , 1 ) results in an empty list

Include Page

	_nav_BackToTop
	_nav_BackToTop

Collections vs. lists

UNIQUE, SUBSET, UNION and INTERSECTION return a collection of data, i.e. double entries are removed. However, COLLECT statements, + and - return a list of data. You have to be aware of this when using the SIZE or UNPACK function.

Example

Statuses = ["a","b","c","d","a","b","c","d"].

Result

The expression "SIZE ( COLLECT Status FROM {Statuses} )" will result in 8. Note that duplicates are not removed.

The expression "SIZE ( UNIQUE ( COLLECT Status FROM {Statuses} ) )" will result in 4, as the unique filters the duplicates.

UI Text Box

type	warning

When the expression is completely evaluated, all duplicates are filtered as well. Using COLLECT Status FROM {Statuses} as a default expression on an attribute, will result in 4 elements.

Space shortcuts

Page tree

Versions Compared

Old Version 13

New Version 14

Key

EACH

ALL

COLLECT FROM [WHERE]

COLLECT FROM NAMED [WHERE]

UNPACK

SIZE

UNIQUE

SUBSET OF

UNION

INTERSECTION

DIFFERENCE

SYMMETRIC_DIFFERENCE

Collections vs. lists