Page History

Learn more about the collection functions EXISTS, EACH, ALL, COLLECT FROM WHERE, COLLECT FROM NAMED WHERE, COLLECT, UNPACK, LIST, SIZE, UNIQUE, SUBSET OF, UNION, INTERSECTION, DIFFERENCE, SYMMETRIC_DIFFERENCE.

Overview

Function	Description
`EXISTS`	Determines

...

Table of Contents

minLevel	2

EXISTS

...

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.

...

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

...

type	note

...

.

...

EACH

...

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

...

.

...

EACH Person WHERE ( Person.age < 18 ) = FALSE

...

ALL

...

Creates a collection of all instances of a specified

...

Syntax

Code Block
ALL entity

entity - Entity of which to collect all its instances.

Return type

collection of entity instances

Examples

...

entity.

...

ALL Parent = [ Parent_1 , Parent_2 ]

...

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

COLLECT FROM [WHERE]

...

Creates 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

UI Text Box

type	warning

A collections contains no duplicates. Intermediary COLLECT expressions can contain duplicates however, please see the note on collections and duplicates.

Examples

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

...

COLLECT Child.name FROM ALL Child = [ “Kim”, “Rick”, “Bob”, “Mary” ]

...

COLLECT Child FROM Teacher[Teacher_2].teaches_Children = [ Child_1, Child_3, Child_4 ]

...

COLLECT Child.hobbies FROM Teacher[Teacher_1].teaches_Children = [ “Reading”, “Dancing”, “Tennis”, “Painting”, “Basketball” ]

...

)

...

.

...

COLLECT FROM NAMED [WHERE]

...

Version of

...

COLLECT FROM [WHERE]

...

for complex nested selections

...

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

UI Text Box

type	warning

A collections contains no duplicates. Intermediary COLLECT expressions can contain duplicates however, please see the note on collections and duplicates.

Examples

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

...

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 )

...

with

...

type	note

...

an alias

...

.

...

UNPACK

...

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:

...

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 with the COLLECT statement however, can contain duplicates. See the note on collections and duplicates for more info.

Syntax

Code Block
UNIQUE ( collection )

collection - A collection of attribute or entity instances.

Examples

Suppose the following model.

...

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)

...

SUBSET OF

...

Extracts 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.

...

UNPACK ( COLLECT Person.name FROM ALL Person WHERE ( Person.SequenceNumber = MIN ( COLLECT Person.SequenceNumber FROM ALL Person ) ) ) = “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 ) ) ) ) = "Ron".
UNPACK ( COLLECT Person.name FROM ALL Person WHERE ( Person.SequenceNumber = MAX ( COLLECT Person.SequenceNumber FROM ALL Person ) ) ) = “Jenny”

Is the inverse of the LIST function.
`LIST`	Creates a list based on a value. Is the inverse of the UNPACK function.
`SIZE`	Determines the size of a collection.
`UNIQUE`	Filters duplicate items from a collection resulting from a subexpression in a larger expression.
`SUBSET OF`	Returns

TRUE if the items in a collection are all present in another

...

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.

...

type	note

...

collection

...

.

...

UNION Adds two collections of the same base type to a new

...

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:

...

collection

...

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”

...

.

...

INTERSECTION

...

Determines the intersection of two collections.

...

Syntax

Code Block
INTERSECTION ( collection1 , collection2 )

...

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 ) = “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

...

DIFFERENCE

...


`DIFFERENCE`	Determines the difference between 2 collections. Returns a collection containing all the items from collection1 that are not present in

...

Syntax

Code Block
DIFFERENCE ( collection1 , collection2 )

...

collection2

...

.

...

Return type

collection

Examples

...

DIFFERENCE ( [ "a", "b", "c"] , [ "c", "d", "e" ] ) = [ "a" , "b" ]

...

DIFFERENCE ( 1 , 1 ) results in an empty list

SYMMETRIC_DIFFERENCE

...

Determines the symmetric difference between two collections.

...

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 )

...

.

...

Return type

collection

Examples

...

SYMMETRIC_DIFFERENCE ( [ "nv" , "bv" ] , [ "NV" ] ) = [ "bv" ]

...

A note on collections and

...

An expression resulting in a collection does not contain duplicates. Please be aware however, that intermediary results of a COLLECT statement can contain duplicates. You have to be aware of this when using the SIZE or UNPACK function.

This is best illustrated with the following examples.

Person instance	Person.Name	Person.Age
Person_1	Kim	24
Person_2	Rick	25
Person_3	Bob	25

COLLECT Person.Age FROM ALL Person = [ 24, 25 ]
SIZE ( COLLECT Person.Age FROM ALL Person ) = 3 (the intermediary collection is [ 24, 25, 25 ]
SIZE ( UNIQUE ( COLLECT Person.Age FROM ALL Person ) ) = 2 (the duplicates in the intermediary collection are filtered by the UNIQUE function)

Now an example with the UNPACK function. We leave out the first instance from the previous example.

Person instance	Person.Name	Person.Age
Person_2	Rick	25
Person_3	Bob	25

COLLECT Person.Age FROM ALL Person = [ 25 ]
UNPACK ( COLLECT Person.Age FROM ALL Person ) will fail, because the collection contains 2 elements [25,25]
UNPACK ( UNIQUE ( COLLECT Person.Age FROM ALL Person ) ) = 25

UI Text Box

type	note

Only the intermediary results of a COLLECT statement can contain duplicates. The functions UNIQUE, UNION, INTERSECTION, DIFFERENCE and SYMMETRIC_DIFFERENCE always return collections without duplicates.

duplicates.

Functions

Include Page

	Collection function EXISTS
	Collection function EXISTS

Include Page

	Collection function EACH
	Collection function EACH

Include Page

	Collection function ALL
	Collection function ALL

Include Page

	Collection function COLLECT FROM WHERE
	Collection function COLLECT FROM WHERE

Include Page

	Collection function COLLECT FROM NAMED WHERE
	Collection function COLLECT FROM NAMED WHERE

Include Page

	Collection function UNPACK
	Collection function UNPACK

Include Page

	Collection function LIST
	Collection function LIST

Include Page

	Collection function SIZE
	Collection function SIZE

Include Page

	Collection function UNIQUE
	Collection function UNIQUE

Include Page

	Collection function SUBSET OF
	Collection function SUBSET OF

Include Page

	Collection function UNION
	Collection function UNION

Include Page

	Collection function INTERSECTION
	Collection function INTERSECTION

Include Page

	Collection function DIFFERENCE
	Collection function DIFFERENCE

Include Page

	Collection function SYMMETRIC_DIFFERENCE
	Collection function SYMMETRIC_DIFFERENCE

Include Page

	A note on collections and duplicates
	A note on collections and duplicates

...

Space shortcuts

Page tree

Versions Compared

Old Version 19

New Version Current

Key

Overview

EXISTS

SIZE

UNIQUE

SUBSET OF

DIFFERENCE

Functions