Page History

...

List

EXISTS

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

Syntax

EXISTS entity [WHERE condition]

• entity - Entity to search for.
• condition - Boolean expression that represents the criterion the instance has to meet.

Return type

• boolean

Examples

Suppose the following data model.

...

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

ALL

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

Syntax

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.

...

• ALL Parent returns a collection of Parent_1 and Parent_2
• ALL Child returns a collection of Child_1, Child_2, Child_3 and Child_4
• ALL Person returns a collection of Parent_1, Parent_2, Child_1, Child_2, Child_3 and Child_4

COLLECT FROM [WHERE]

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

Syntax

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.

...

• 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”

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

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

• entity or attribute - Entity or attribute to collect.
• 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.hasChild is a relation from Person to Person.

...

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

COLLECT Parent.name 
FROM (
  COLLECT Person 
  FROM ALL Person 
  WHERE (Person.hasChild != ?)) NAMED Parent
}

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

To retrieve all children names, we use this expression:

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

This expression results in a collection of “Kim”, “Bob” and “Sam”.

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

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 the instances “Bob” and “Sam”.

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

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 of “Kim”.

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

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 in a collection of “Kim”.

UNPACK

Use this function to get the content of a single-valued collection.

Syntax

UNPACK collection

• collection - A collection 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))) results in “Ron”
• UNPACK(COLLECT Person.name FROM ALL Person WHERE (Person.SequenceNumber = MAX(COLLECT Person.SequenceNumber FROM ALL Person))) results in “Jenny”

Conversion

TEXT(integer, [format], [locale])
TEXT(number, [format], [locale])
TEXT(currency, [format], [locale])
TEXT(percentage, [format], [locale])
TEXT(date, [format])
TEXT(dateTime, [format])
TEXT(boolean, [format])

INTEGER(string, [format], [locale])
INTEGER(number)
INTEGER(currency)
INTEGER(percentage)
INTEGER(boolean)

NUMBER(string, [format], [locale])
NUMBER(integer)
NUMBER(currency)
NUMBER(percentage)
NUMBER(boolean)

CURRENCY(string, [format], [locale]
CURRENCY(integer)
CURRENCY(number)

PERCENTAGE(string, [format], [locale]
PERCENTAGE(integer)
PERCENTAGE(number)

DATE(string, [format])
DATE(dateTime)

DATETIME(string, [format])
DATETIME(date)

BOOLEAN(string, [format])

...