Page History
Panel | |||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||
COLLECT FROM NAMED [WHERE]Use this version Version of the COLLECT FROM [WHERE] function for complex nested selections in which it is necessary to use with an alias. Syntax
Inputs
Return type
Suppose the following model with only instances of entity
To retrieve all the parent names, we use this expression:
This expression results in “Rick”, “Julia” and “Joan”. To retrieve all children names, we use this expression:
This expression results in a collection of three instances of To retrieve all children younger than 15, we use this expression:
This expression results in a collection of two instances of To retrieve the children’s names with a parent older than 40 years:
This expression results in a collection with only the name “Kim”. You can make it as complex as you like, for example:
This expression results also in a collection with only the name “Kim”.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||
Panel | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|
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 theUNPACK
cannot resolve a list with two elements. To solve this theUNIQUE
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”
Back to top |
---|
bgColor | white |
---|
LIST
Use this function to create a list based on a value. It is the inverse of the UNPACK function.
Syntax
Code Block |
---|
LIST ( attribute/collection ) |
- attribute - An attribute of any data type.
- collection/list - A collection or list of values.
Return type
- a multivalued list with entries of any type
Examples
Suppose the following data model.
LIST ( COLLECT Person.name FROM ALL Person ) = [ "Bob" , "Jane" , "Mary" , "Rick" , "Ron" , "Jenny" ]
- In this example the
LIST
function adds no value, as the result of theCOLLECT
is already a list.
- In this example the
LIST ( COLLECT Person.SequenceNumber FROM ALL Person ) = []
- As Jenny has an unknown sequence number, the result of the
COLLECT
expression is?
. TheLIST
function creates an empty list in case the parameter has?
value.
- As Jenny has an unknown sequence number, the result of the
Back to top |
---|
bgColor | white |
---|
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:
UI Text Box | ||
---|---|---|
| ||
SIZE and COUNT are similar except for ?: SIZE ( ? ) results in 0, while COUNT ( ? ) results in ? |
Back to top |
---|
bgColor | white |
---|
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.
Return type
- collection
Examples
Suppose the following model.
Back to top |
---|
bgColor | white |
---|
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
Venn diagram
Examples
Given Person.hobbies = [ "Tennis" , "Soccer" ]
Given Person.hobbies = [ ? ]
Given Person.hobbies = [ "Tennis" , "Soccer" ]
Person.hobbies SUBSET OF [ ? ] = FALSE
1 SUBSET OF Address.Numbers
Given Adress.Numbers = ?
UI Text Box | ||
---|---|---|
| ||
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. |
Back to top |
---|
bgColor | white |
---|
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
Venn diagram
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[Parent_2].Has_Children )
Parent[Parent_2].Has_Children.name )
Back to top |
---|
bgColor | white |
---|
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 - Collections to be intersected. These collections must be of the same base type.
Return type
- collection
Venn diagram
Examples
Suppose the following model. Entity Teacher
has a multivalued relation with entity Child
via the relation Teacher.teaches_Children
.
Back to top |
---|
...
bgColor | white |
---|
DIFFERENCE
This function returns a collection containing all the items from collection1 that are not present in collection2.
Syntax
Code Block |
---|
DIFFERENCE ( collection1 , collection2 ) |
...
- collection1, collection2 - Collections to be compared. These collections must be of the same base type.
Return type
- collection
Venn diagram
Examples
...
Back to top |
---|
...
bgColor | white |
---|
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 compared. These collections must be of the same base type.
Return type
- collection
Venn diagram
Examples
...
Back to top |
---|
...
bgColor | white |
---|
A note on collections and duplicates
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, or when using TSL.
This is best illustrated with the following examples.
...
TSL:
The ages present are: [[[ COLLECT Person.Age FROM ALL Person ]]].
...
Now an example with the UNPACK function. We leave out the first instance from the previous example.
...
type | note |
---|
...
|