SELECT
| Specification | Value |
|---|---|
| Description | Gets objects that match clauses. |
| Parameter 1 Name | Repository |
| Parameter 1 Type | repository |
| Parameter 2 Name | Clauses (optional, but highly recommended) // For more information, see the section below) |
| Parameter 2 Type | operators (WHERE, TOP) |
| Parameter 3 Name | - |
| Parameter 3 Type | - |
| Return value | Returns a list of objects from Repository that matches Clauses. |
Note
SELECT returns a limited number of records - 20 000. This limit is only for the returned records count.
It's recommended to set as many filters as possible. (in the WHERE clause).
Example:
The [WHERE] clause of the SELECT statement supports the following operators:
Operators not supported by SELECT but used by the FILTER operator are:
FILTER AND WHERE filtering of a SELECT
Having this in mind, it's really important to extract lists from the database.
When using SELECT, you send a request to the database with as many [WHERE] filters as possible.
If there's an unsupported filter, then the list returned by SELECT may be filtered additionally by [FILTER].
A SELECT statement makes a direct request to the database, which may influence productivity and slow down the calculation of the attribute. When using SELECT, you have to apply as many [WHERE] filters as possible, because this would limit the amount of data extracted from the database into the client.
If SELECT doesn't provide enough filters, the result may be filtered by [FILTER], which operates on data already loaded in the client.
Examples:
Let's say you need a list of documents whose DocumentTypeId is equal to 'bbd8e7ae-c0e0-4c1b-8730-7d68fa52971e' or '89ca5ca4-ad57-44c7-9b33-2ff44e054bff'. The documents are work orders.
The following calculated attribute would be incorrect and return errors when used:
10: SELECT REPO:Production.ShopFloor.WorkOrders EXP:20
20: WHERE EXP:30
30: OR EXP:40 EXP:50
40: EQUAL CONST:bbd8e7ae-c0e0-4c1b-8730-7d68fa52971e
45: ATTRIB:DocumentTypeId CONST:System.Guid
50: EQUAL EXP:45 CONST:89ca5ca4-ad57-44c7-9b33-2ff44e054bff
You can set an attribute which selects the work orders.
Then, to filter the list which the SELECT operator returned, apply the [FILTER] operator for more precision.
The correct calculated attribute is as follows:
10: FILTER EXP:20 EXP:30
20: SELECT REPO:Production.ShopFloor.WorkOrders
30: OR EXP:40 EXP:50
40: EQUAL EXP:45 CONST:bbd8e7ae-c0e0-4c1b-8730-7d68fa52971e
45: CAST ATTRIB:DocumentTypeId CONST:System.Guid
50: EQUAL ATTRIB:DocumentTypeId CONST:89ca5ca4-ad57-44c7-9b33-2ff44e054bff