Nscale Query Language
The nscale Query Language (NQL) is a language which can be used to query information stored by nscale Server Application Layer.
The nscale Server Application Layer API (Advanced Connector) provides several search methods which demand an NQL string as parameter to determine the submitted search. Parts of the NQL can also be used to configure the nscale Server Application Layer, for example to define visibility rules or formatted properties.
The syntax of NQL is adapted from SQL syntax and is explained in this document. Be aware that NQL is a textual representation of an equivalent object search model (see class SearchControl of the Advanced Connector API). It is recommended to use the object search model instead of an NQL statement to enhance performance (an NQL statement has to be parsed and transformed into a SearchControl before a search can be executed). This document describes the complete NQL of nscale Server Application Layer of version 7.17.
1. Basic Structure
An NQL instruction is made up of a maximum of the following elements:
All elements are optional (so the empty string is a valid NQL expression), however the order of the elements is prescribed. If an element is specified, it can itself contain required and optional elements. All language elements with the exception of function names, context variables and nscale object names can be provided in lower or upper case. Function names, context variables, and nscale object names such as properties or object class names must match the upper and lower case conventions that are specified or created in the system.
In a first step the NQL parser tries to parse an NQL statement by using the much simpler NQL 6 grammar (in nscale 6 there were no query functions, instant properties, filtered properties, aggregate functions and so on). If this fails (e.g. because query functions are used) in a
second step the NQL parser tries to parse the NQL statement by using the much more complex NQL 7 grammar. It is possible though to give the parser a hint to use NQL 7 grammar already in the first step to prevent the needless try with NQL 6 grammar when nscale 7 functionality is
contained. This is done by appending the characters \7 as a prefix of the NQL statement, e.g. \7 select up(displayname) .
|
2. The select clause
The properties to be returned are determined by the select clause. The select clause starts with key word select
, followed by an arbitrary number of comma-separated properties (or alternatively a wildcard *, see examples below). Temporarily calculated expressions, called instant formatted properties or shortened instant properties, can also be included in the select clause. An instant property is determined by a QueryOperand, which is one of a property, a constant, a context variable or a function.
Note that besides provided properties the returned result set always contains key information for every contained row. So, if no further information is necessary, the select clause may be omitted.
2.1. Examples
-
select *
(strictly disadvised) -
select creationdate, displayname, objectclass, version
-
select displayname, yearcreation=year(initialcreation)
In the last example, the instant property with alias yearcreation contains the year of creation of a resource. For an instant property, an alias should be provided, however in nscale Server Application Layer of at least version 7.11 this is not mandatory anymore. Nevertheless it is recommended to use an alias in productive code, as clients may fail over a missing alias (e.g. for compatibility reasons). An alias may be omitted for testing purpose, e.g. in the nscale Query Tester.
It is strictly disadvised to use select *, as this results in an overload of the database. It is only provided for convenience and testing purpose and should not be used in productive code. There are even more convenience abbreviations which are meant for testing purpose and should not be used in productive code, for example: |
-
select *\(f)
returns all properties except fulltext properties -
select *\(c)
returns all properties except computed properties -
select *\(m)
returns all properties except formatted properties -
select *\(f, c, m)
combination of the three above -
select *\(mv)
returns all properties except multi-value properties -
select *\(sv)
returns all properties except single-value properties -
select *\(sp)
returns all properties except system properties -
select *\(cp)
returns all properties except custom properties -
select *\(v)
returns all properties except virtual properties -
select *\(pv)
returns all properties except pseudo-virtual properties -
select *\(s)
returns all properties except string properties -
select *\(i)
returns all properties except integer properties -
select *\(l)
returns all properties except long properties -
select *\(d)
returns all properties except double properties -
select *\(b)
returns all properties except boolean properties -
select *\(dt)
returns all properties except date properties -
select *\(ts)
returns all properties except timestamp properties -
select *\(bt)
returns all properties except blob properties -
select *\(aq)
returns all properties except area qualified identifier properties -
select *\(_s)
returns all properties except non-string properties -
select *\(_i)
returns all properties except non-integer properties -
select *\(_l)
returns all properties except non-long properties -
select *\(_d)
returns all properties except non-double properties -
select *\(_b)
returns all properties except non-boolean properties -
select *\(_dt)
returns all properties except non-date properties -
select *\(_ts)
returns all properties except non-timestamp properties -
select *\(_bt)
returns all properties except non-blob properties -
select *\(_aq)
returns all properties except non-area qualified identifier properties
3. The where clause
The where clause starts with key word where
followed by a condition which must be met by a resource in nscale Server Application Layer in order that it is part of returned result set. The condition may consist of several expressions which are OR or AND combined.
An expression of a condition consists of a left hand side and a right hand side, combined by an operator. The left hand side and the right hand side must be a QueryOperand, which is one of a property, a constant, a context variable, a function or a list of these. Supported operators are:
Operator | Description |
---|---|
|
The equal operator |
|
The not equal operator; alternative representation is |
|
The greater operator |
|
The greater or equal operator |
|
The smaller operator |
|
The smaller or equal operator |
|
The in operator; note that the right hand side of an in operator must be a list; alternative representation is |
|
The between operator to search for an including interval; note that the right hand side of a between operator must be a list which contains exactly two elements; alternative representation is |
|
The like operator to search for matching resources; the wildcard * stands for an arbitrary number of characters while the wildcard ? stands for exactly one character; wildcards are escaped by the \ character; alternative representation is |
|
The is null operator to search for undefined values; note that the right hand side of a is null operator must be omitted; alternative representation is |
|
The is not null operator to search for defined values; note that the right hand side of a is not null operator must be omitted; alternative representation is |
|
The soundex equals operator to search for values which sounds similar (Soundex algorithm); alternative representation is |
|
The similar to operator to search for values which accomplish a certain similarity (Levenshtein algorithm); alternative representation is |
|
The equals ignore case operator to search for case insensitive values; alternative representation is |
|
The like ignore case operator to search for matching resources (see like operator, but ignore case); alternative representation is |
|
The matches operator to search for resources which match a regular expression; alternative representation is |
All elements of a condition may be enclosed in parentheses to improve legibility or to raise the priority of a sub condition. As an AND condition has a stronger binding than an OR condition, to get expected results, an appropriate parenthesis is necessary. Example:
where displayname like 'a*' and (resourcetype = 1 or resourcetype = 2)
Note that the left hand side and the right hand side of an expression must have the same data type. However, integers and longs can be mixed together, as well as dates and timestamps. In the latter case, for dates a timestamp midnight is considered.
If an expression contains a property of type boolean, the operator and the right hand side may be omitted for convenience and to improve legibility. So instead of where hasnote = true
it is sufficient to request where hasnote
.
To negate an expression, a NOT condition can be used. So instead of where hasnote != true
it is also possible to request where not hasnote
.
For multi-value properties, a special handling is necessary. A multi-value property in a condition must be used inside of an EXISTS condition. In turn, all properties inside of an EXISTS condition must belong to the same multi-value scope. Example:
where exists(itemcontenttype = 'text/plain' and itemlength > 100)
This condition finds all resources whose multi-value scope contentiteminfo contains an entry with content type text/plain and a length > 100.
To sum it up, a condition is a representation of one of the following components:
Name | Format | Remarks |
---|---|---|
Expression |
|
Note that for operator is null and is not null the right hand side query operand must be omitted |
AND-Condition |
|
Alternative representation is |
OR-Condition |
|
Alternative representation is |
NOT-Condition |
|
Alternative representation is |
EXISTS-Condition |
|
Alternative representation is |
Note that the where clause is optional in principle, but usually it is reasonable to restrict the number of rows of a returned result set, so it should rarely be omitted.
3.1. Examples
In the following examples, we assume that there is a single-value property lastname of type string, as well as a multi-value scope CARS containing the properties car of type string and color of type string.
NQL | Description |
---|---|
|
Finds all resources where property displayname equals Invoice |
|
Finds all resources where property displayname begins with Invoice |
|
Finds all resources where property displayname does not begin with Invoice |
|
Finds all resources where property displayname is null |
|
Finds all resources where property displayname is not null |
|
Finds all resources where lifecyclestate is finalized (1), to be archived (2) or archived (3) |
|
Finds all resources where lifecyclestate is indexed (0) |
|
Finds all resources where current version has been created between January 1st 2016 and December 31th 2016 |
|
Finds all resources where lastname sounds like Smiths, e.g. Smith, Smyth, Smythe etc. |
|
Finds all resources where lastname is similar to Smiths, e.g. Smith, Smyth, Smythe etc. |
|
Finds all resources where lastname equals ignore case Smiths |
|
Finds all resources where color contains an entry red |
|
Finds all resources where color contains an entry red or an entry green |
|
This statement is equivalent to the former one |
|
Finds all resources where color contains an entry red and an entry green. |
|
Does not find any resource (an entry in color cannot be red and green at the same time). |
|
Finds all resources where color does contain an entry which is not red |
|
Finds all resources where color does not contain an entry red |
|
Finds all resources where color does not contain an entry which is not red |
|
Finds all resources where color does contain at least one entry |
|
Finds all resources where color does not contain any entry |
|
Finds all resources where multi-value scope CARS contains a blue Mercedes |
|
Finds all resources where car contains a Mercedes and color contains blue |
Be aware that the result of a where clause may differ for every database dialect, as every database dialect has a different behavior in detail (e.g. Oracle does not distinguish between null and an empty string). This is also true for the orderby clause. |
4. The orderby clause
The orderby clause starts with key word orderby
or alternatively order by
. With the orderby clause the sorting of the query results can be determined. This is done by specifying the property according to which the sorting should take place and, optionally, the sort order (asc (ascendant) or desc (descendant)). The property may also be an instant property. By specifying further properties the sub-sorting of results can be specified. The syntax is:
orderby <property name> [asc|desc][, <property name> [asc|desc], ...]
If sort order is not specified, the result set will be sorted in ascending order.
Note that sorting by a multi-value property is not supported. To overcome this restriction, it is possible though to define an appropriate value property which uses for example query function listEntry or
toFlat on the multi-value property.
|
4.1. Examples
-
orderby initialcreation desc, identifier
-
order by displayname
-
orderby year(creationdate) desc, displayname
-
orderby length(displayname) asc, displayname
5. The paging element
Paging allows to specify a segment of the actual hits of a search which is returned as result. This is especially useful when a search would return a large number of rows. It enables to iterate through the actual result set by submitting subsequent searches, each with a small number of rows. For the paging element, the page number (starting by 1) and the page size can be specified, e.g. a page number 1 and a page size 100 returns the first 100 hits (the first page) of a search.
Note that the order of the entries of a page is specified by the orderby clause. If the orderby clause is omitted or does not determine a unique order, it is possible that duplicate entries may occur in different pages and on the other hand entries may be missing at all. To overcome this behavior, it is recommended to add an orderby clause which determines a unique order when using paging, e.g. order by identifier for the scope Repository.
|
5.1. Examples
NQL | Description |
---|---|
|
Returns the first 100 hits (the first page) of a search |
|
This statement is equivalent to the former one |
|
Returns the second 100 hits (the second page) of a search |
|
Returns the first 25 hits (the first page) of a search |
|
Returns the first 1000 hits (the first page) of a search |
|
Returns the first 50 hits (the first page) of a search |
|
Returns the second 50 hits (the second page) of a search |
|
Returns the first 30 hits (the first page) of a search |
|
Returns the first 100 hits (the first page) of a search |
|
Returns the first 100 hits (the first page) of a search. Caution: for this notation, the page number is zero-based! |
|
Returns the first 50 hits (the first page) of a search. |
|
Returns the second 100 hits (the second page) of a search. Caution: for this notation, the page number is zero-based! |
|
Returns the first 100 hits (the first page) of a search. |
6. The scope element
The scope element is only effective for searches in Repository and in UserManagement.
In Repository, the scope element can be used to restrict the search to the current folder or, additionally, expand it to include the subfolders of the current folder.
With scope onelevel
the search is restricted to current folder while with scope subtree
the search is expanded to all subfolders of the current folder. If no scope is specified, scope onelevel
takes effect.
In UserManagement, a search with scope onelevel
initiates a search based on Principal and join to PrincipalInfo, while a search with scope subtree
initiates a search based on PrincipalInfo and join to Principal.
So, a search with scope onelevel
finds users, positions and groups of the configured domains, but no additional or orphaned principal info objects. In turn, a search with scope subtree
finds all existing principal
info objects, but no positions (positions do not have additional information) and no principals who do not have default principal info yet.
6.1. Examples
-
select displayname
returns display name of all resources of current folder -
select displayname scope onelevel
equivalent to the statement before -
select displayname scope subtree
returns display name of all resources of subtree of current folder
7. The count element
The count element is only useful in a search with paging. If this element is used, the total number of results is determined and returned. This can be useful if e.g. only the first 100 hits are requested but the actual number of hits should also be provided.
7.1. Examples
-
paging (1, 50) count
-
[0, 0] scope subtree count
In the first example, the rows 1 to 50 and the total number of hits are returned. In the second example an empty result set is returned but the total number of hits is provided. Note that the count element initiates a parallel select count search in the database which can be rather time consuming (for the second example only the select count search is initiated). So the count element should only be used, when the actual row count is really needed. Note that when the count element is omitted, the result set of a search at least contains information, whether more results are available.
8. The hidden element
The hidden element is only effective for searches in Repository and in Workflow.
Normally, resources for which the system attribute hidden respectively processhidden is set are not taken into account when evaluating an NQL query. If the hidden element is specified in an NQL query, however, these objects are taken into account.
8.1. Examples
-
where resourcetype = 2 and hidden scope subtree hidden
-
select processhidden hidden
In the first example, all hidden documents are returned. In the second example, hidden as well as not hidden processes are returned, identified by selected property processhidden.
9. Properties
Note that properties are also often called attributes, these two notations are synonymous. For every different scope (like Repository, Workflow, Masterdata etc.) there is a set of properties provided which can be used in NQL. The set of properties may consist of system defined as well as user defined properties. Whether a property can be used in the select, where or orderby clause depends on several settings, like the type of a property or whether it is single-valued or multi-valued.
9.1. Scope Repository and Workflow
For the scopes Repository and Workflow it is easy to decide, whether a property can be used in the select, where or orderby clause. Every property in these scopes has three boolean flags Selectable, Searchable and Sortable. If a property is Selectable, it may be used in the select clause. If a property is Searchable, it may be used in the where clause. If a property is Sortable, it may be used in the orderby clause. Note that for the flags Searchable and Sortable there are some restrictions, which means not for every property it is allowed to set these flags to true.
A property is not allowed in the where or orderby clause when
-
it is of type Blob
-
it is a system computed property
-
it is a custom computed property which depends on other necessary properties
-
it is a formatted property which depends on other non-searchable or non-sortable components
-
it is a fulltext property
A property is also not allowed in the orderby clause when
-
it is multi-valued
Note that it is possible though to sort over the first entry of a multi-value property by creating a value property with the format entry(myMultivalueProperty, 0)
. Another
option is to create a value property with format toFlat(myMultivalueProperty)
.
9.2. Scope Masterdata, Principal (UserManagement) and Calendar (Collaboration)
There are some differences to the scopes Repository and Workflow. First, there are no computed properties in these scopes, and in scopes Principal and Calendar there are even no formatted properties. Second, in scopes Principal and Calendar there are no user defined properties, the set of provided properties is fixed and defined by system. In turn, in scope Masterdata there are no system properties but only user defined properties. The resulting constraints are:
-
a property of type Blob is not sortable; it is only searchable when using operator
is null
oris not null
-
a multi-value property is not sortable
-
a formatted masterdata property may only be searchable respectively sortable when the contained components are searchable respectively sortable
9.3. Notations
The name of a property is case-sensitive, so the use of upper and lower letters is essential. In scopes Repository and Workflow, it is possible to create a property which references a masterdata key property. If such a reference is created, all properties of corresponding masterdata scope are provided as virtual properties and it is also possible to copy these properties in actual scope (so called pseudo-virtual properties). The notation for virtual properties is <masterdata key reference>:<masterdata scope>$<masterdata property>
. The notation for pseudo-virtual properties is <masterdata key reference>$<masterdata scope>$<masterdata property>
.
10. Constants
Constants may be used in the select, where and orderby clause. For the different types, the following syntax is required:
Type | Description |
---|---|
|
A constant of type string must be quoted either in single quotes ' or in double quotes ". If the string itself contains the quoting sign, the quoting sign must be escaped by the quoting sign itself (double quoting sign). |
|
A constant of type boolean must be either |
|
A constant of type integer is a number in the range of a java Integer |
|
A constant of type long is a number in the range of a java Long |
|
A constant of type double is a number in the range of a java Double, the decimal divider is a dot, e.g. 3.14 |
|
A constant of type date must be in format |
|
A constant of type timestamp must be in format |
|
A constant of type area qualified identifier must consist of the name part of the area qualified identifier as string |
A list of constants must be set in parentheses, e.g. (1, 2, 3)
. Note that sorting by a constant is not reasonable and is not supported by many database dialects, so something like orderby 87
should not be used.
10.1. Examples
NQL | Description |
---|---|
|
Returns given constant |
|
The same as above but with alias x |
|
Returns constants foo as string, 1 as long and true as boolean |
|
Finds all resources which have been created in 2017 or later |
|
Finds all resources which have at least one note or no rendition |
|
Finds all resources with objectclass D1, D2 or D3; the property objectclass of type area qualified identifier demands string constants (the name part of the area qualified identifier) |
|
Finds all folders, documents and links |
|
Sorts the result set by the first character of the displayname; in this case, the use of constants 0 and 1 in the orderby clause is reasonable, as they are used in a function which also contains a property |
10.2. Convenience constants
For special properties which act like enumerations, NQL provides constants which can be used for convenience:
Property | Constants | Example |
---|---|---|
resourcetype (scope Repository) |
$folder, $document, $link, $annotation, $note, $rendition, $container, $addition |
|
lifecyclestate (scope Repository) |
$indexed, $finalized, $toBeArchived, $archived, $error |
|
deletestate (scope Repository) |
$none, $logicallyDeleted, $logically, $automaticallyDeleted, $automatically |
|
linkreferencetype (scope Repository) |
$softlink, $hardlink |
|
subtype (scope Repository) |
$xsap, $mail, $vcard, $calendar, $partition, $office, $lta, $file, $signature, $sapilm |
|
type (scope UserManagement) |
$user, $position, $group |
|
11. Context Variables
The nscale Server Application Layer provides several context variables. The value of a context variable depends on the context of the current request. The following context variables are available:
Context Variable | Type | Description |
---|---|---|
|
String |
Provides the principal id of current user (the user who initiated current request) |
|
String |
Provides the principal id of the default position of current user |
|
List < String > |
Provides the principal ids of all positions of current user |
|
List < String > |
Provides the principal ids of all groups (hierarchical) of current user |
|
List < String > |
Provides the principal ids of all organizational entities where the current user has proxy competences for; note that proxy competences are transitive |
|
List < String > |
Provides the principal ids of all organizational entities where the current user has head competences for (flat supervisor competence) |
|
List < String > |
Provides the principal ids of all organizational entities where the current user has manager competences for (hierarchical supervisor competence) |
|
List < String > |
Provides the principal ids of all organizational entities where the current user has owner competences for; reading access to principal folders of owned organizational entities is granted |
|
List < String > |
Provides the principal ids of all positions where the current user has agent competences for; an agent competence on a position allows to login as principal who owns the position |
|
String |
Provides the principal id of a principal who acts as an agent in current session, e.g. who logged in as a principal by using agent competences |
|
String |
Provides the work position id of current user which must be the id of an existing position of current user or null; if not null, workflow requests are executed in the context of this id by default |
|
Date |
Provides the current date |
|
Timestamp |
Provides the current date and time |
|
Timestamp |
Provides the current date at midnight |
|
Timestamp |
Provides the next date at midnight |
|
String |
Provides the name of the host which sent current request |
|
String |
Provides the ip address of the host which sent current request |
|
String |
Provides the name of the nscale application which sent current request |
|
String |
Provides the locale of the client application which sent current request |
|
String |
Provides a token which has been set in current session by the client application (user defined value without any meaning for the nscale Server Application Layer) |
|
Double |
Provides the configured threshold for a similarity search as a value between 0 and 1; two strings are considered as similar, when the similarity algorithm returns a value greater than or equal to this value |
|
String |
Provides the system id, uniquely identifying an nscale Server Application Layer system |
11.1. Examples
NQL | Description |
---|---|
|
Returns the locale of the client application which sent current request, e.g. de or en_US |
|
Finds all resources which have been created by current user |
|
Finds all workflow instances where current user has been involved. Note that |
|
Finds all resources which have been created today |
12. Query Functions
The nscale Server Application Layer provides 95 query functions which can be used in the select, where and orderby clause. The parameters of a query function are QueryOperands which can be one of a property, a constant, a context variable, a query function or a list of these. Note that a parameter of a query function which is parenthesized is automatically converted to a list. For most query functions most parameters support single-values and multi-values. In general, when at least one parameter is multi-valued, the following logic is implemented (there are exceptions from this rule though):
-
the return value is multi-valued
-
the length of returned list is determined by the length of the longest list of all list parameters
-
all list parameters which are shorter are logically filled up with null values
-
a single-value parameter behaves like a multi-value parameter where all list entries contain the single value
-
the query function is executed line by line for every row
Be aware that the result of a query function in the select clause may differ from the result in the where or orderby clause, as the result of the select clause is calculated in the nscale Server Application Layer while the result of the where and orderby clause is calculated in the database. This means, the result of the where and orderby clause depends on the implementation of the function within the database (which actually may be different for every database dialect). Be also aware that the (extensive) use of query functions in the where or orderby clause may decrease the performance of searches drastically, so use them with care. This is in particular the case when nested query functions are used (query functions inside of query functions). |
Note that some of the functions are not searchable and/or not sortable, because there is no equivalent function in the database (this may depend on used database dialect). Whenever this is the case, it is mentioned in the descriptions below. However, these restrictions are only effective, when a function contains properties as parameters of the function, otherwise the value of the function results in a constant which can be calculated before the search is executed. In this case, a function is always searchable (it could also be sortable, but sorting by a constant is not reasonable). The provided query functions can be categorized in the following groups:
-
convert functions
-
mathematical functions
-
string functions
-
date/timestamp functions
-
list functions
-
other functions
12.1. Convert Functions
There are 10 convert functions. These functions convert the type of an operand into another type.
12.1.1. ToString
NQL |
|
Return type |
String |
Parameters |
|
Description |
Converts given operand to string. |
Examples
NQL | Description |
---|---|
|
Converts property lifecyclestate to string |
|
Finds all resources with state finalized |
|
Sorts by objectclass name |
12.1.2. ToInteger
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Converts given operand to integer. |
Examples
NQL | Description |
---|---|
|
Converts property hasnote to integer, all resources having at least one note return 1, the others return 0 |
12.1.3. ToLong
NQL |
|
Return type |
Long |
Parameters |
|
Description |
Converts given operand to long. |
Examples
NQL | Description |
---|---|
|
Converts property deletestate to long |
|
Returns the milliseconds of initialcreation since epoch |
12.1.4. ToDouble
NQL |
|
Return type |
Double |
Parameters |
|
Description |
Converts given operand to double. |
Examples
NQL | Description |
---|---|
|
Converts property lifecyclestate to double |
12.1.5. ToBoolean
NQL |
|
Return type |
Boolean |
Parameters |
|
Description |
Converts given operand to boolean. |
Examples
NQL | Description |
---|---|
|
Converts property deletestate to boolean, all resources which have been deleted logically return true |
12.1.6. ToDate
NQL |
|
Return type |
Date |
Parameters |
|
Description |
Converts given operand to date. |
Examples
NQL | Description |
---|---|
|
Converts property creationdate to date, which means the time part is cut off |
|
Finds all resources which have been created today |
12.1.7. ToDatetime
NQL |
|
Return type |
Timestamp |
Parameters |
|
Description |
Converts given operand to timestamp. |
Examples
NQL | Description |
---|---|
|
Returns today with time midnight (redundant to context variable %todayAtMidnight) |
12.1.8. ToBlob
NQL |
|
Return type |
Blob |
Parameters |
|
Description |
Converts given operand to blob. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a blob (byte array) of length 3, containing entries 1, 2 and 3 |
12.1.9. ToAQI
NQL |
|
Return type |
AreaQualifiedIdentifier |
Parameters |
|
|
|
Description |
Converts given operand to area qualified identifier. The second parameter determines the type of area qualified identifier, e.g. |
Examples
NQL | Description |
---|---|
|
Returns objectclass name F0 |
12.1.10. BaseConvert
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Converts given number from source base to target base, e.g. from base 10 to base 16 to get the hexadecimal representation. Supported are any bases between 1 and 36 (including). This function is searchable and sortable only for source base 10 and target base 16 and vice versa. |
Examples
NQL | Description |
---|---|
|
Converts the property storagelayerarcid from hexadecimal to binary system; the first 8 signs are omitted (otherwise, an overflow would occur) |
|
Finds all resources where the decimal value of property storagelayerarcid is bigger than 1000; the first 8 signs are omitted (otherwise, an overflow would occur) |
12.2. Mathematical Functions
There are 15 mathematical functions. Note that a mathematical function always returns a defined value, as null values are replaced by the neutral element of the mathematical function, e.g. 0 for the plus function or 1 for the product function.
12.2.1. Plus
NQL |
|
Return type |
Double or Long, depending on type of parameters |
Parameters |
|
Description |
Summates given operands. |
Examples
NQL | Description |
---|---|
|
Returns the sum of given operands, which is 6 |
|
Returns the sum of given operands, which is a list with entries 13, 11 and 10 |
12.2.2. Minus
NQL |
|
Return type |
Double or Long, depending on type of parameters |
Parameters |
|
|
|
Description |
Subtracts given subtrahends from given minuend. |
Examples
NQL | Description |
---|---|
|
Returns 2 |
12.2.3. Product
NQL |
|
Return type |
Double or Long, depending on type of parameters |
Parameters |
|
Description |
Multiplies given operands. |
Examples
NQL | Description |
---|---|
|
Finds all resources where the product of integer properties i1 and i2 is bigger than 10 |
|
Sorts by product of integer properties i1 and i2 |
12.2.4. Quotient
NQL |
|
Return type |
Double |
Parameters |
|
|
|
Description |
Divides given dividend by given divisor. |
Examples
NQL | Description |
---|---|
|
Returns 0.5 |
12.2.5. Modulo
NQL |
|
Return type |
Double or Long, depending on type of parameters |
Parameters |
|
|
|
Description |
Calculates remainder when integer dividing given dividend by given divisor. |
Examples
NQL | Description |
---|---|
|
Returns the remainder of integer dividing 10 by 3 which is 1 |
12.2.6. Sqrt
NQL |
|
Return type |
Double |
Parameters |
|
Description |
Calculates square root of given operand. |
Examples
NQL | Description |
---|---|
|
Returns 3 |
12.2.7. Pow
NQL |
|
Return type |
Double |
Parameters |
|
|
|
Description |
Calculates the exponential power of given base and given exponent. |
Examples
NQL | Description |
---|---|
|
This is equivalent to the former example with function sqrt |
|
Returns 32 |
12.2.8. Log
NQL |
|
Return type |
Double |
Parameters |
|
|
|
Description |
Calculates the logarithm of given operand and given base. |
Examples
NQL | Description |
---|---|
|
Returns 6 |
|
Returns 3 |
12.2.9. Floor
NQL |
|
Return type |
Long |
Parameters |
|
Description |
Rounds down given operand. |
Examples
NQL | Description |
---|---|
|
Returns 5 |
12.2.10. Ceiling
NQL |
|
Return type |
Long |
Parameters |
|
Description |
Rounds up given operand. |
Examples
NQL | Description |
---|---|
|
Returns 6 |
12.2.11. Round
NQL |
|
Return type |
Double or Long, depending on given precision |
Parameters |
|
|
|
Description |
Rounds given operand with given precision. A precision of 0 rounds to an integer value; a positive value rounds the decimal part, while a negative value rounds the integer part. |
Examples
NQL | Description |
---|---|
|
Returns 6 |
|
Returns 5.6 |
|
Returns 5.64 |
|
Returns 10 |
12.2.12. Abs
NQL |
|
Return type |
Double or Long, depending on type of operand |
Parameters |
|
Description |
Eliminates the leading sign of given operand. |
Examples
NQL | Description |
---|---|
|
Returns 7 |
12.2.13. Signum
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Returns sign of given operand, which is -1 for negative values and 1 for positive values, else 0. |
Examples
NQL | Description |
---|---|
|
Returns -1 |
12.2.14. Sinus
NQL |
|
Return type |
Double |
Parameters |
|
Description |
Calculates the sinus of given operand. |
Examples
NQL | Description |
---|---|
|
Returns 0 |
12.2.15. Avg
NQL |
|
Return type |
Double |
Parameters |
|
Description |
Calculates the average of given operands. Note that null values are ignored. |
Examples
NQL | Description |
---|---|
|
Returns 2.5 (null values are ignored) |
12.3. String Functions
There are 20 string functions. These functions operate on query operands of type string.
12.3.1. Concat
NQL |
|
Return type |
String |
Parameters |
|
Description |
Concats given operands. A null operand behaves like an empty string. |
Examples
NQL | Description |
---|---|
|
Returns the concatenation of property displayname, a dot and property fileextension |
12.3.2. Substring
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Returns a substring within a string from given starting position, optionally with given length. The index is 0-based. |
Examples
NQL | Description |
---|---|
|
Finds all resources whose displayname begins with an A |
|
Cuts off first character from displayname |
12.3.3. Instring
NQL |
|
Return type |
Integer |
Parameters |
|
|
|
|
|
|
|
Description |
Identifies the position of a substring within a string, optionally with given starting position and/or indication whether last occurrance is searched for. The index is 0-based. If substring does not exist, -1 is returned. When lastIndex is true, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the position of first character a in displayname |
|
Returns the position of first character a in displayname, beginning at the fourth character (index is 0-based) |
|
Returns the position of last character a in displayname, beginning at the first character (index is 0-based) |
12.3.4. Replace
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
|
|
Description |
Replaces a substring within a string by another string. The substring to be replaced (target) may be a regular expression, in this case a regular expression policy must be specified which can be 0 for policy replace first or 1 for policy replace all. When a regular expression policy is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Replaces characters xx by characters yyy in displayname |
|
Replaces all numbers in displayname by character x |
|
Replaces first number in displayname by the empty string |
12.3.5. ReplaceCharacters
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Replaces characters within a string by other characters. The position of the replace characters (the second parameter) correlates to the position of the replacement characters (the third parameter), e.g. the first character in the second parameter is replaced by the first character in the third parameter. The length of the replace characters should normally equal the length of the replacement characters. If the length of replace characters is longer, then the extra characters are deleted. If the length of replace characters is shorter, then the extra replacement characters are ignored. |
Examples
NQL | Description |
---|---|
|
Replaces all characters 'a' by 'x', all characters 'b' by 'y' and all characters 'c' by 'z' in displayname |
|
Replaces all digits in displayname by the empty string, which means removes all digits from displayname |
|
Returns Thrii Chinisi With i Diibli Biss |
|
Returns 1.000,1 |
12.3.6. Trim
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Returns given string operand trimmed. It is possible to execute function trim (0), ltrim (negative number) or rtrim (positive number) by specifying a style. Also the character to be trimmed may be specified. When a character different from blank is specified or style is not a constant, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns abc without leading and trailing blanks |
|
Returns abc without leading blanks |
|
Returns abc without trailing blanks |
|
Returns Hello World |
12.3.7. ToLower
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Returns given string operand in lower case. It is also possible to specify a locale. When a locale is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns hello world |
12.3.8. ToUpper
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Returns given string operand in upper case. It is also possible to specify a locale. When a locale is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns HELLO WORLD |
12.3.9. Capitalize
NQL |
|
Return type |
String |
Parameters |
|
Description |
Returns given string operand where all words are capitalized, which means the first character of all words is in upper case while all following characters are in lower case. A new word is identified by a delimiter which is a blank or a special character. |
Examples
NQL | Description |
---|---|
|
Returns Hello World |
|
Returns Abc-Def/Ghi |
12.3.10. Reverse
NQL |
|
Return type |
String |
Parameters |
|
Description |
Reverses given operand. |
Examples
NQL | Description |
---|---|
|
Returns cba |
12.3.11. Replicate
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Replicates given operand for specified times. |
Examples
NQL | Description |
---|---|
|
Returns please please please |
12.3.12. Pad
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
|
|
Description |
Pads given string operand. The length parameter determines the length of returned string, while the pad parameter determines the string which is padded to given operand. The style operand determines, whether the operand is padded on the left or right hand side. A positive number results in a right padded operand, otherwise the operand is left padded. |
Examples
NQL | Description |
---|---|
|
Returns 0000123 |
|
Returns 123++ |
12.3.13. Base64
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Encodes or decodes given string operand to respectively from base 64 encoding. It is also possible to specify, whether base 64 code is url-encoded. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns base 64 encoded displayname |
|
Returns Hello |
|
Returns Hello |
12.3.14. Hash
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Executes given hash algorithm on given string operand. The algorithm must be a numeric value between 0 and 4, where 0 represents MD5, 1 represents SHA-1, 2 represents SHA-256, 3 represents SHA-384 and 4 represents SHA-512. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns MD5 hash of property displayname |
|
Returns a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e |
12.3.15. StringFormat
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Formats the given string format operand in given locale, using given parameters. The string is formatted by using static |
Examples
NQL | Description |
---|---|
|
Returns 0000123 (seven-digit number with leading zeros) |
|
Returns property contentlength containing thousands separator in German style |
|
Returns property contentlength in KB in German style |
|
Returns property contentlength in MB in style of current locale, rounded to two decimal digits |
|
Returns property myDoubleProperty containing thousands and decimal separator in English style, rounded to three decimal digits |
12.3.16. Soundex
NQL |
|
Return type |
String |
Parameters |
|
Description |
Returns the soundex-code of given operand. The soundex function can be used to search for similar sounding entries. It returns a four character string containing the phonetic representation of its input parameter. Words that are spelled differently, but sound alike, should match to the same return value, which makes it possible to perform a similarity search. Note that the soundex algorithm was invented for the English language but should also produce good results in related languages like German. |
Examples
NQL | Description |
---|---|
|
Finds resources where displayname sounds like Smiths |
12.3.17. SoundexDifference
NQL |
|
Return type |
Integer |
Parameters |
|
|
|
Description |
Returns the soundex difference between given operands. The soundex function returns a four character string containing the phonetic representation of its input parameter. The soundex difference function compares two results of a soundex function, for each character that is identical the return value of the soundex difference function is increased by one. Thus, the returned value is a number between 0 and 4 where 4 is the highest similarity. Note that the soundex algorithm was invented for the English language but should also produce good results in related languages like German. |
Examples
NQL | Description |
---|---|
|
Finds resources with a soundex difference of 3 or 4 |
12.3.18. EditDistance
NQL |
|
Return type |
Integer |
Parameters |
|
|
|
|
|
Description |
Returns the number of steps which are necessary to convert one string into another by using the Levenshtein algorithm, so it can be used to search for entries with a specified similarity. It is possible to specify, whether comparison is case sensitive or not. When third parameter is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Finds resources with an edit distance of 0, 1 or 2 |
12.3.19. Similarity
NQL |
|
Return type |
Double |
Parameters |
|
|
|
|
|
Description |
Returns a value between 0 and 1 where 0 means the two given strings are totally different and 1 means the two given strings are identical. It is possible to specify, whether comparison is case sensitive or not.
The algorithm to determine similarity of two strings is: |
Examples
NQL | Description |
---|---|
|
Finds resources with a similarity between 0.6 and 1 |
12.3.20. ColognePhonetic
NQL |
|
Return type |
String |
Parameters |
|
Description |
Returns a string containing the phonetic representation of its input parameter. Words that are spelled differently, but sound alike, should match to the same return value, which makes it possible to perform a similarity search. Note that in contrast to the soundex function, the cologne phonetic function is optimized for the German language. This function is not searchable and not sortable (it may be used for a similarity search though by defining appropriate value properties). |
Examples
NQL | Description |
---|---|
|
Returns the cologne phonetic code of property displayname |
12.4. Date/Timestamp Functions
There are 12 date/timestamp functions. These functions operate on query operands of type date or timestamp.
12.4.1. Year
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the year from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the year of creation of a resource |
|
Finds resources where current version has been created in current year |
12.4.2. Month
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the month from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the month of creation of a resource as a value between 1 and 12 |
12.4.3. Day
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the day from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the day of creation of a resource as a value between 1 and 31 |
12.4.4. Hour
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the hour from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the hour of current time as a value between 0 and 23 |
12.4.5. Minute
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the minute from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the minute of current time as a value between 0 and 59 |
12.4.6. Second
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Extracts the second from given date/datetime operand. |
Examples
NQL | Description |
---|---|
|
Returns the second of current time as a value between 0 and 59 |
12.4.7. DateExtract
NQL |
|
Return type |
Integer |
Parameters |
|
|
|
Description |
Extracts specified part from given date/datetime operand. The given extract must be a numeric value between 0 and 5 where every number represents a different part of the date/datetime operand. A value 0 extracts the day of the week, e.g. in Germany for Monday the value 1 is returned. A value 1 extracts the day of the week in a month, e.g. value 2 is returned for the second Monday of a month. A value 2 extracts the day of the year, that is the number of days which have passed since the beginning of the year. A value 3 extracts the week of the month, e.g. value 1 is returned for the first week of a month. A value 4 extracts the week of the year, e.g. value 2 is returned for the second week of a year. A value 5 extracts the quarter of the year, e.g. value 3 is returned for months July, August and September. When parameter extract is not a constant, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the day of the week as a value between 1 and 7 |
|
Returns the day of the week in a month as a value between 1 and 5 |
|
Returns the day of the year as a value between 1 and 366 |
|
Returns the week of the month as a value between 1 and 5 |
|
Returns the week of the year as a value between 1 and 53 |
|
Returns the quarter of the year as a value between 1 and 4 |
|
Finds resources which have been created in the first quarter |
12.4.8. DateRound
NQL |
|
Return type |
Date or Timestamp |
Parameters |
|
|
|
|
|
Description |
Rounds given date/datetime by using specified rounding policy. The rounding policy is represented by a numeric value. If rounding policy is 0 or null, the returned date is not altered. If rounding policy is a negative number, the returned date is rounded down. If rounding policy is a positive number, the returned date is rounded up. The absolute number of the rounding policy determines the kind of rounding, it must lie between 1 and 8, representing rounding by year, month, week, day, hour, minute, second or quarter. When rounding up and the given date/datetime is at the beginning of specified rounding part, the returned date is unaltered, e.g. rounding up by year on January 1st returns given date/datetime unaltered. Note that it is also possible to specify a business calendar used for rounding. When a business calendar is specified, only rounding by day is supported, so a negative rounding policy would result in rounding down to the beginning of the business day and a positive rounding policy would result in rounding up to the end of the business day. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Rounding up current date to the beginning of the next year |
|
Rounding down current date to the beginning of current year |
|
Rounding up current date to the beginning of the next month |
|
Rounding down current date to the beginning of current month |
|
Rounding up current date to the beginning of the next week |
|
Rounding up current datetime to the beginning of the next day |
|
Rounding up current datetime to the beginning of the next hour |
|
Rounding up current datetime to the beginning of the next minute |
|
Rounding up current datetime to the beginning of the next second |
|
Rounding up current date to the beginning of the next quarter |
|
Rounding down current datetime to the beginning of current business day, regarding business calendar with name 'NordrheinWestfalen' |
12.4.9. DateFormat
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Formats given date/datetime operand in given string format. If format is null the default format 'yyyy-MM-dd HH:mm:ss' takes effect. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns date of initial creation in given format |
|
Returns the name of the day of initial creation in Finnish language |
12.4.10. LocaleDateFormat
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
|
|
Description |
Formats given date/datetime operand in given style and locale. The style must be a numeric value between 0 and 5, where 0 means full style, 1 means long style, 2 means medium style and 3 means short style. Style 4 returns the name of the day and style 5 returns the name of the month. The locale must be a string in ISO639 format, e.g. 'en_US' or 'de'. If style and/or locale are null default values take effect. The default value for date and time style is 2 which means medium style. The default value for locale is the locale of current session if provided, else the locale of the default dictionary if existing, else the default language of the jvm. Note that when time style is specified, the locale must also be specified. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns date of initial creation in default format (medium style) and in default language |
|
Returns date of initial creation in full style in French language |
|
Returns date of initial creation in full date style and medium time style in Finnish language |
|
Returns the name of the day of initial creation in English language |
12.4.11. DateAdd
NQL |
|
Return type |
Date or Timestamp |
Parameters |
|
|
|
|
|
|
|
|
|
Description |
Adds a number to given date part of given date. The date part must be a numeric value between 0 and 5, representing part year, month, day, hour, minute or second. If date part is null the default date part day (represented by value 2) takes effect. If an optional business calendar is specified, only business times and dates are considered. It is also possible to specify a rounding policy. When a business calendar and/or rounding policy is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a date where 5 months are added to the current date |
|
Returns a datetime where 7 days are subtracted from the date of initial creation |
|
Returns a datetime where 5 business hours are added to creation date, regarding business calendar with name 'NordrheinWestfalen' |
|
Finds resources which have been created in the last seven days |
12.4.12. DateDiff
NQL |
|
Return type |
Long |
Parameters |
|
|
|
|
|
|
|
Description |
Calculates the difference between two dates concerning given date part. The date part must be a numeric value between 0 and 5, representing part year, month, day, hour, minute or second. If date part is null the default date part day (represented by value 2) takes effect. If an optional business calendar is specified, only business times and dates are considered. When a business calendar is specified, this function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the difference in months between the date of initial creation and the current date |
|
Returns the difference in days between the date of initial creation and the archive date |
|
Returns the difference in business days between the date of initial creation and the creation date of the current version, regarding business calendar with name 'NordrheinWestfalen' |
|
Finds resources where the difference of the date of initial creation and the creation date of the current version is greater than 10 days |
12.5. List Functions
There are 12 list functions. These functions operate on multi-value query operands.
12.5.1. ConcatLists
NQL |
|
Return type |
List of any type, depending on type of parameters |
Parameters |
|
Description |
Concatenates all list operands. The resulting list contains all entries of the given lists. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a list which contains the group ids, the position ids and the user id of the current principal; note that the context variable %currentUserPrincipalId must be embraced by brackets to cast the single value to a list |
|
Finds workflow instances where at least one of current principal’s positions or groups is in assigned pool |
12.5.2. ListEntry
NQL |
|
Return type |
Any type, depending on type of first parameter |
Parameters |
|
|
|
Description |
Extracts an entry from given list operand. The list index is 0-based. If list index does not exist, null is returned. When used in the where clause, this function must not be nested in other functions. |
Examples
NQL | Description |
---|---|
|
Returns the id of the first group of current principal |
|
Finds resources where first content item is of type text/plain |
12.5.3. ListIndex
NQL |
|
Return type |
Integer |
Parameters |
|
|
|
Description |
Identifies the (first) index of given entry from given list operand. The returned list index is 0-based. If list entry does not exist, -1 is returned. When used in the where clause, this function must not be nested in other functions. |
Examples
NQL | Description |
---|---|
|
Returns 1 |
12.5.4. ListLength
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Returns the size of given list operand. When used in the where clause, this function must not be part of a case condition. |
Examples
NQL | Description |
---|---|
|
Returns the number of groups of current principal |
|
Finds resources which reference an nscale Server Storage Layer document |
12.5.5. Sublist
NQL |
|
Return type |
List of any type, depending on type of first parameter |
Parameters |
|
|
|
|
|
Description |
Returns a sublist of given list operand, determined by given starting index and optionally given length. The index is 0-based. When used in the where clause, this function must not be nested in other functions. |
Examples
NQL | Description |
---|---|
|
Returns a list with entries 'c', 'd' and 'e' |
|
Returns a list with entries 'c' and 'd' |
|
Finds resources with a content item of type text/plain, first content item omitted |
12.5.6. ListEntries
NQL |
|
Return type |
List of any type, depending on type of parameter |
Parameters |
|
Description |
May be used as parameter of a function with an arbitrary number of parameters, which means this function must always be nested. The list entries function provides all entries of given list operand. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the sum of all entries of multi-value property itemlength |
|
Returns the maximum value of all entries of multi-value property itemlength |
|
Returns the average value of all entries of multi-value property itemlength |
12.5.7. ToFlat
NQL |
|
Return type |
String |
Parameters |
|
|
|
|
|
Description |
Concatenates the entries of given list operand to a string with given delimiter. If delimiter is omitted, the string '; ' is used as default delimiter. If parameter lastDelimiter is provided, the last two entries are delimited by given string. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the length of all content items, delimited by '; ' |
|
Returns the ids of the groups of current principal, delimited by '#' |
|
Returns the names of the groups of current principal, delimited by ', ' respectively ' and ' for the last two entries |
12.5.8. Tokenize
NQL |
|
Return type |
List of String when operand is of type String, List of Integer when operand is of type Blob |
Parameters |
|
|
|
Description |
Tokenizes given operand by given delimiter to a list. If delimiter is omitted, the string ';' is used as default delimiter. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a list whose entries are the parts of the ip address of current client |
12.5.9. UniqueList
NQL |
|
Return type |
List of any type, depending on type of first parameter |
Parameters |
|
|
|
Description |
Eliminates duplicate entries from a list. It is also possible to specify, whether null values are excluded from the list. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a list with the two entries 'red' and 'yellow' |
12.5.10. SortList
NQL |
|
Return type |
List of any type, depending on type of first parameter |
Parameters |
|
|
|
|
|
|
|
Description |
Sorts given list. It is possible to specify, whether list is sorted ascending or descending and whether sorting is case sensitive or not. It is also possible to specify, where null values are positioned, e.g. at the end or at the beginning of the list or whether null values are filtered. Positive values determine that null values are considered as greatest values. Negative values determine that null values are considered as smallest values. A value of 0 determines that null values aren’t sorted (they are omitted). This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns multi-value property itemlength sorted in ascending order |
|
Returns multi-value property itemlength sorted in descending order |
|
Returns multi-value property itemlength sorted in ascending order where null values are positioned at the end of the list |
12.5.11. ReduceList
NQL |
|
Return type |
List of any type, depending on type of first parameter |
Parameters |
|
|
|
Description |
Eliminates given operands from given list. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a list with the two entries 'blue' and 'green' |
|
Returns an empty list (all entries from multi-value property itemlength are eliminated from itself) |
12.5.12. ToList
NQL |
|
Return type |
List of any type, depending on type of parameters |
Parameters |
|
Description |
Returns a list which contains all given operands. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a list with the three entries 1, 2 and 3 |
|
Returns a list with the three entries a, b and c |
12.6. Other Functions
There are 26 more functions which could not be categorized in the sections above.
12.6.1. Null
NQL |
|
Return type |
Null |
Parameters |
|
Description |
The null function always returns null. Note that the null function is the only function where brackets may be omitted. |
Examples
NQL | Description |
---|---|
|
Returns null |
|
Returns hello world |
12.6.2. NullIf
NQL |
|
Return type |
Type of left hand side parameter |
Parameters |
|
|
|
Description |
Returns null, if the two parameters are equal, else the left hand side parameter is returned. |
Examples
NQL | Description |
---|---|
|
Returns null |
|
Returns null, if displayname equals 'Hello World', else the displayname |
12.6.3. Coalesce
NQL |
|
Return type |
Any type, depending on type of parameters |
Parameters |
|
Description |
Returns the first operand which is not null. |
Examples
NQL | Description |
---|---|
|
Returns usercommonname if not null, else username if not null, else displayname |
12.6.4. Length
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Returns the length of given operand. |
Examples
NQL | Description |
---|---|
|
Returns the number of characters of the displayname |
|
Finds resources where the length of the displayname is greater than 10 |
12.6.5. Max
NQL |
|
Return type |
Any type, depending on type of parameters |
Parameters |
|
Description |
Returns the maximum value of an arbitrary number of operands. An operand which evaluates to null is ignored. If all operands are null, null is returned. Note that all operands must be of same type. |
Examples
NQL | Description |
---|---|
|
Returns 10 |
12.6.6. Min
NQL |
|
Return type |
Any type, depending on type of parameters |
Parameters |
|
Description |
Returns the minimum value of an arbitrary number of operands. An operand which evaluates to null is ignored. If all operands are null, null is returned. Note that all operands must be of same type. |
Examples
NQL | Description |
---|---|
|
Returns 2 |
12.6.7. Case
NQL |
|
Return type |
Any type, depending on type of parameters |
Parameters |
|
|
|
|
|
Description |
Evaluates a condition (see where clause) and returns either the first or the second operand, depending on the result of the condition. Note that the true and false operand must be of same type. When the case function is part of the where clause, the result of the function must not be a list. |
Examples
NQL | Description |
---|---|
|
Returns 'I am red' if the multi-value property colour contains the entry 'red', else 'I am not red' is returned |
|
Returns null if user defined property int1 is greater than 10 and smaller than 20, else the display name is returned |
|
Alternative representation of the statement above |
|
Yet another alternative; note that for this notation brackets must be omitted |
12.6.8. Switch
NQL |
|
Return type |
Any type, depending on type of parameters |
Parameters |
|
|
|
|
|
Description |
Evaluates an expression and returns the first value for which the expression is true. The switch function contains three parameters: a switch operand, an optional operator and an array of operands. The switch operand is compared with other values. The operator defines the kind of comparison, the default operator is equals. The array of operands consists of tuples (ordered pairs), where the first operand of a tuple is compared with the switch operand and the second operand of a tuple is the value which is returned when a comparison returns true. If the first operand of a tuple is null, this is considered as the default tuple whose value is returned when no other expression returns true. The switch function is rather redundant to the case function, but it is more concise. Note that operator between is not supported. When the switch function is part of the where clause, the switch operand and the result of the function must not be a list. |
Examples
NQL | Description |
---|---|
|
Returns 'Folder', 'Document' or 'Link', depending on the type of resource |
|
Returns an aggregated fulltext state |
|
Returns 'New' for resources which have been created in current year, else 'Old' |
|
Returns 'Empty' if displayname is null, else the displayname |
|
Returns indication whether resource has a note as text |
|
Returns true if R + L = J, else false |
12.6.9. Random
NQL |
|
Return type |
Double |
Parameters |
|
|
|
Description |
Returns a random number between 0 (inclusive) and 100 (exclusive) by default. The upper and lower border can be adjusted by specifying a factor and an offset. The default factor is 100, specifying another value will increase or decrease the upper border. The specified offset is subtracted from factor and added to the random number, e.g. a factor of 50 and an offset of 10 will return a random number between 10 (inclusive) and 50 (exclusive). This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a random number between 0 and 100 |
|
Returns a random number between 0 and 10 |
|
Returns a random number between 5 and 10 |
12.6.10. Translate
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Translates the given operand in given locale. The locale must represent an existing dictionary of the configuration, e.g. 'en_US' or 'de'. If the given locale does not exactly match the name of a dictionary, the next best dictionary is chosen, e.g. when locale 'de_AT' is requested, assuming there is no Austrian dictionary but there is a German dictionary 'de', then 'de' is taken as dictionary (and vice versa). If given locale is null the default locale takes effect which is the locale of current session if provided, else the locale of the default dictionary if existing, else the default language of the jvm. If such a dictionary does not exist, the operand is not translated and is returned unmodified. Otherwise the given operand is taken as a key value of the determined dictionary, the returned value is the corresponding translated phrase. The translate function is not sortable. When it contains any properties, its use in the where clause is restricted by several constraints, e.g. it cannot be used in a NOT-Operation or in a NULL-expression or when the condition contains any OR-operations or in an EXISTS-operation when in-operator is used. Note also that a translation must exist (i.e. an entry in requested dictionary) in order that a row can be found (no fallback to requested key). |
Examples
NQL | Description |
---|---|
|
Returns 'Entscheidung' (the translation for key 'Decision' of dictionary 'de_AT' if existing, else 'de') |
12.6.11. PrincipalIdResolver
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Formats one or more principal ids in given style. The style must be a numeric value between 0 and 8, where 0 means name style (e.g. 'admin'), 1 means full name style (e.g. 'admin@nscale'), 2 means common name style (e.g. 'nscale Administrator') and 3 means common name style with fallback to name style. The latter one means, the common name is returned if it is not null, else the name is returned. Style 4 is the same as style 3 except that for a non existing principal id the principal id is returned instead of 'unknown'. Style 5 returns the domain name and style 6 returns the type of the principal. Style 7 returns the referenced principal which is the default position in case of a user and the associated user in case of a position. Style 8 resolves a full name to a principal id (priority is user before position before group). If style is null, the common name with fallback style takes effect as default. The principal id resolver function is not allowed in sort order items. When it contains any properties, its use in a condition is restricted by several constraints, e.g. it cannot be used in a NOT-Operation or in a NULL-expression or when the condition contains any OR-operations or in an EXISTS-operation when in-operator is used. Note also that deleted principals cannot be found. |
Examples
NQL | Description |
---|---|
|
Returns the initial creator in default style |
|
Returns the initial creator in full name style |
|
Finds all resources when current principal belongs to domain 'nscale' |
|
Finds all resources when current principal is 'admin@nscale' |
12.6.12. GroupResolver
NQL |
|
Return type |
List of String |
Parameters |
|
|
|
|
|
Description |
Returns all group ids of given principal id, representing a user, position or group. It is also possible to determine whether groups are considered flat or hierarchical and whether all or only active groups are returned. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns ids of all groups of current user |
|
Returns names of all explicit assigned groups of all positions of current user |
|
Returns ids of all explicit assigned groups of all positions of current user which are active |
12.6.13. CompetenceResolver
NQL |
|
Return type |
List of String |
Parameters |
|
|
|
|
|
|
|
Description |
Returns all principal ids where given principal has a competence of given type for. Given principal id may represent a user, position or group. If given principal id represents a user, all positions of the user are considered (when active flag is set to true, only active positions are considered). The type must be a numeric value between 1 and 6 where 1 means head, 2 means proxy, 3 means owner, 4 means local group admin, 5 means manager and 6 means agent competence. For competences of type head, proxy and manager it is possible to determine, whether implicit competences are considered. If set to true, for type proxy also transitive proxies are returned. For type head and manager also members of groups are returned (flat for head and hierarchical for manager). It is also possible to determine whether only active competences are considered. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns ids of all explicit proxies of current user |
|
Returns names of all implicit headed principals of current user |
|
Returns ids of all implicit managed principals where current user’s default position has an active competence for |
12.6.14. ReferenceResolver
NQL |
|
Return type |
Any type, dependent on type of resolved property |
Parameters |
|
|
|
|
|
|
|
Description |
Resolves a property of a referenced resource. This function is determined by a reference id, a category and a property name. A referenced resource may be a repository resource, a workflow instance, a masterdata, a principal(-info), a calendar, or an element. The reference id may be a numeric id or a string representation of a referenced resource, e.g. a resource id or a workflow instance id etc. The category must be a numeric value between 1 and 6, where 1 means repository, 2 means workflow, 3 means element (not yet supported), 4 means masterdata, 5 means principal or principal info and 6 means calendar. The property must be the name of a property which exists in given category or a query operand which contains at least one property which exists in given category. Providing an optional area name is only supported for categories repository, workflow and masterdata. In categories repository and workflow, the area name must be the name of an existing document area. In category masterdata, the area name must be the name of an existing masterdata scope. Note that resolving of resources may cause performance problems, as the read permissions are evaluated for every resource of a result set. If the referenced id is an id of a principal and only the name is supposed to be resolved, use function PrincipalIdResolver instead (does not need any validation of permissions). Caution: the property to be resolved should be deployed as a constant and NOT a property itself, as this produces unpredictable results. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns the display name of the parent folder of a resource |
|
Returns the email addresses of current principal |
|
Returns the display name of resource with identifier 42 in document area with name 'AnotherDocumentArea' |
12.6.15. ValueSetResolver
NQL |
|
Return type |
String |
Parameters |
|
|
|
Description |
Resolves an entry in given value set. If data of given value set is in expected format (e.g. the format used by nscale Cockpit or nscale Web) the entry with given key is considered and corresponding value is returned. If given key does not exist, the key is returned as value. If given value set does not exist, the nscale Server Application Layer tries to find a value set which is represented by given name, e.g. if given value set name is the suffix of an existing value set where the prefix represents a locale, the locale of the client application is taken into account, see examples below. If no value set can be found, the key is returned as value. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns Mr or Herr, depending on locale of the client application (note that this value set is provided when user scenario is enabled) |
|
Returns Herr (note that this value set is provided when user scenario is enabled) |
12.6.16. KeyGenerator
NQL |
|
Return type |
String or Long, depending on whether a uuid or a sequential number is requested |
Parameters |
|
|
|
|
|
Description |
Generates either a uuid or a numeric id. If the function is called without any parameter, a uuid of type String is returned. If a key generator is specified, a numeric id of type Long is generated by given key generator. If given key generator is null, the default (plugins) key generator is considered. Note that referenced key generator must exist (either a KeyGeneratorDefinition or a RangeKeyGeneratorDefinition of given name), else an exception is thrown. For a numeric id, a lower and an upper bound may be specified, to determine the interval from which a number is generated. When an interval is specified, the referenced key generator should be a range key generator, specifying upper and lower bounds for a non range key generator is not reasonable. When an interval is specified for a range key generator and there is no more free number left in this interval, null is returned. This function is not searchable and not sortable. |
Examples
NQL | Description |
---|---|
|
Returns a uuid |
|
Returns a numeric id from default (plugins) key generator |
|
Returns a numeric id, generated by key generator with name 'myKeyGenerator' |
|
Returns a numeric id between 1000 and 2000 (inclusive), generated by range key generator with name 'myRangeKeyGenerator' |
12.6.17. BitAnd
NQL |
|
Return type |
Long |
Parameters |
|
|
|
Description |
Calculates a bitwise AND of two numeric operands. An operand which evaluates to null is replaced by value 0. |
Examples
NQL | Description |
---|---|
|
Returns 12 |
|
Finds resources where fulltext indexing caused an error (not classified permanent) |
12.6.18. BitOr
NQL |
|
Return type |
Long |
Parameters |
|
|
|
Description |
Calculates a bitwise OR of two numeric operands. An operand which evaluates to null is replaced by value 0. |
Examples
NQL | Description |
---|---|
|
Returns 47 |
12.6.19. LogicalAnd
NQL |
|
Return type |
Boolean |
Parameters |
|
Description |
Returns true when all given parameters resolve to true, else false is returned. |
Examples
NQL | Description |
---|---|
|
Returns true |
12.6.20. LogicalOr
NQL |
|
Return type |
Boolean |
Parameters |
|
Description |
Returns true when at least one of given parameters resolves to true, else false is returned. |
Examples
NQL | Description |
---|---|
|
Returns true |
12.6.21. LogicalXOr
NQL |
|
Return type |
Boolean |
Parameters |
|
Description |
Returns true when exactly one of given parameters resolves to true, else false is returned. |
Examples
NQL | Description |
---|---|
|
Returns false |
12.6.22. MatchesNumeric
NQL |
|
Return type |
Boolean |
Parameters |
|
Description |
Evaluates whether a string represents a numeric value (could be converted to a number). |
Examples
NQL | Description |
---|---|
|
Returns true |
|
Returns false |
|
Sorts a result set numerically, if displayname contains values which can be converted to numbers |
12.6.23. Matches
NQL |
|
Return type |
Boolean |
Parameters |
|
|
|
Description |
Evaluates whether a string matches a regular expression. |
Examples
NQL | Description |
---|---|
|
Returns true |
|
Returns false |
12.6.24. Ascii
NQL |
|
Return type |
Integer |
Parameters |
|
Description |
Returns the ascii code of a character (the first character of a string). |
Examples
NQL | Description |
---|---|
|
Returns 65 |
|
Returns ascii codes of all characters of property displayname |
12.6.25. Char
NQL |
|
Return type |
String |
Parameters |
|
Description |
Returns the character represented by an ascii code. |
Examples
NQL | Description |
---|---|
|
Returns A |
|
Returns the displayname |
12.6.26. Custom
NQL |
|
Return type |
Defined by custom function |
Parameters |
Defined by custom function |
Description |
Executes a previously defined custom function. A custom function can be defined in two ways, either by configuring a CustomFunctionDefinition or by implementing a plugin of type CustomComputedIndexingPropertyDefinitions whose handler class implements interface CustomFunctionsHandler. The number and type of parameters as well as the type of returned value depends on the definition of the custom function. |
Examples
NQL | Description |
---|---|
|
Executes previously defined custom function with name FooBar |
|
Equivalent to the former statement |
|
Equivalent to the former statement |
13. Aggregate Searches
NQL also supports aggregate searches. The basic structure of an aggregate search is the same as for a normal search with two exceptions. First, the count clause is not supported. Second, the select clause is mandatory. This is evident as an aggregate search does not return key information, so an aggregate search without a select clause would return nothing. The following aggregate functions are supported:
Function | Description |
---|---|
|
Returns number of values which are not null |
|
Returns maximum of all values |
|
Returns minimum of all values |
|
Returns average of all values |
|
Returns sum of all values |
|
Returns number of different values which are not null |
|
Returns all different values (duplicates are omitted) |
Every aggregate function allows exactly one QueryOperand as parameter, where the QueryOperand must be either a property or a query function. If a query function is used, there must be at least one property used inside of the (maybe nested) parameters of the query function. Note that the aggregate functions max, min, avg and sum are ambiguous to query functions of same name. If they are used in an aggregate search, the aggregate function is presumed, when the function only has one parameter, else the query function is presumed (the aggregate function only allows one parameter while the use of a query function with only one parameter is senseless). Note also that aggregate function distinct may only be used for the first property of the select clause. Also, when using distinct, the use of other aggregate functions is useless.
Aggregate function distinct does not work in MS SQL Server, when paging is used. |
13.1. The select clause
There are only a few deviating characteristics for the select clause of an aggregate search, which are described here. NQL allows an arbitrary number of aggregate functions in the select clause as well as properties which do not use an aggregate function. Every property which does not use an aggregate function is grouped, which means it is part of the groupby clause. Note that NQL does not have an explicit groupby clause but the groupby clause is set implicitly. Be aware that the resulting values of properties of the select clause are calculated in the database, so in an aggregate search all used properties and query functions must be searchable.
Additionally, computed properties are allowed in the select clause which only need exactly one necessary property to be calculated. In this case, the grouping takes place on the necessary property.
When using aggregate function count
it is advised to count over a property which does not have null values (like identifier
in Repository or processidentifier
in Workflow etc.), as using other properties may produce unpredictable results.
13.2. The where clause
There are only a few deviating characteristics for the where clause of an aggregate search, which are described here. Aggregate functions may also be used in the where clause. In SQL, this would result in a having clause, but NQL does not have an explicit having clause but the having clause is set implicitly. Note that when aggregate functions are used in the where clause, all used properties in the where clause which are not used in an aggregate function must also be used in the select clause.
13.3. The orderby clause
There are only a few deviating characteristics for the orderby clause of an aggregate search, which are described here. Aggregate functions may also be used in the orderby clause. Note that in the orderby clause only properties may be used which are also part of the select clause.
13.4. Examples
NQL | Description |
---|---|
|
Returns the number of all resources in a folder (of course only visible resources are considered) |
|
Returns the number of all resources in the sub-tree of a folder |
|
Returns the number of all resources in a folder, grouped by their resource type. So, assuming there is at least one folder, document and link inside of the folder, the returned result set contains three rows, containing the number of documents, folders and links in the first column and the corresponding resource type in the second column |
|
Returns the same result as the search before, but sorts it by resource type in descending order |
|
Returns the same result as the search before, but sorts it by the number of resources per resource type |
|
Returns the number of all resources in a folder, grouped by their resource type, where the number of resources per resource type is greater than 10 |
|
Returns the number of all documents and links in a folder, grouped by their resource type, where the number of resources per resource type is greater than 10, ordered by number and resource type |
|
Returns the number of all resources in a folder, grouped by their resource type and the year of creation |
|
Returns the number of resources grouped by the first character of the display name in upper case |
|
Returns the number of resources per folder |
|
Returns the maximum display name, the minimum display name and the average of all lifecycle states |
|
Returns the maximum display name and the minimum display name per resource type |
|
Returns the maximum creation date, the minimum creation date and the maximum and minimum year of creation |
|
Returns the sum of the length of all content items of all documents |
|
Returns the sum of the length of all content items of all documents, grouped by their content type |
|
Returns duplicate display names and the number of their occurrences |
|
Returns the number of different display names |
|
Returns all different display names |
14. Subqueries
NQL also supports subqueries in form of instant filtered properties (shortened filtered properties). A filtered property may be used in the list of requested properties (select clause) and in the condition (where clause).
14.1. The select clause
In the select clause, a filtered property may be used in two ways. Either it is used to restrict the returned values of a multi-value property or it is used to retrieve the value of an aggregate property by building a subquery.
In the first case, a filtered property consists of a target property and a condition, separated by the \
character. The target property must be a multi-value property and the condition must only contain properties of the same multi-value scope as the target property.
The effect is, that the returned values for the multi-value property are filtered by specified condition. Note that the condition must not contain itself a filtered property.
As for instant formatted properties, an alias may be used for a filtered property. So the syntax for a filtered property to restrict the returned values of a multi-value property is:
[alias=]targetProperty\condition
14.1.1. Examples
NQL | Description |
---|---|
|
Returns the multi-value property itemcontenttype, where all entries are omitted which are not text/plain (the returned lists are filtered) |
|
The same search as above but with an alias |
|
Returns the multi-value property itemcontenttype, where all entries are omitted which are smaller than 1000 bytes |
|
Returns three properties, column two contains multi-value property itemcontenttype, column three contains multi-value property itemlength and column one contains filtered property x, which contains all entries of itemcontenttype where the type is text/plain or image/tiff and the length is greater than 100 |
The second case to use a filtered property in the select clause is to retrieve the value of an aggregate property. The basic structure of a filtered property for this case is the same as for the first case, so there is a target property and a condition.
In contrast to the first case however, the target property may be a single-value property, the condition is optional and the filtered property must always be parenthesized. The target property must be an aggregate property, as the subquery must return a unique value.
Furthermore, an additional component may be used in a filtered property, a bind property. A bind property may be specified to bind a property of the subquery with the same property of the superior select (a kind of join of the two statements). The bind property starts
with the @
character. When bind is used without specifying a property (by just declaring the @
character), the primary key is bound. So the syntax for a filtered property to retrieve the value of an aggregate property is:
[alias=](targetProperty[@[bindProperty]]\[condition])
14.1.2. Examples
NQL | Description |
---|---|
|
Returns the maximum displayname of all entries of returned result set, the returned value is identical in all rows |
|
Returns the maximum length of all displaynames of returned result set, the returned value is identical in all rows |
|
Returns the maximum length of all displaynames of returned result set per resourcetype, the returned value is identical for all rows with identical resourcetype |
|
Returns the padded displayname, the length of all returned values is determined by the maximum length of all displaynames of returned result set |
|
Returns the content length, the sum of all content lengths and the percentage of content length compared to the overall length of all contents, rounded to two decimal digits |
14.2. The where clause
A filtered property in the where clause behaves like a subselect. The basic structure of a filtered property in the where clause is the same as in the second case for the select clause, so there is a target property, a condition and a bind property. In contrast to the select clause however, an alias must not be used and the condition may contain itself filtered properties (a subselect inside of a subselect).
In addition, there is another optional component in a filtered property in the where clause, a list of filter facet properties. Besides the target property, all other components are optional. Filter facet properties may be used, when the target property is an
aggregate property. The effect is, that the filter facet properties are grouped (they are part of the implicit groupby clause). The filter facet properties start with the |
character and are comma-separated.
In general, a subselect always returns a list, so for a subselect only operator in
is supported. Sole exception is when the target property is an aggregate property and there are no filter facet properties used. In this case, the return value of
the subselect is unique, so more operators may be used like =
, !=
, <
, <=
, >
or >=
.
A subselect in the where clause must always be parenthesized. So the syntax for a filtered property in the where clause is:
(targetProperty[|filterFacetProperty[,...]][@[bindProperty]]\[condition])
14.2.1. Examples
NQL | Description |
---|---|
|
Finds all resources of level 2 |
|
Finds all resources of level 3 |
|
Finds all empty folders |
|
Finds all resources where the value of integer property int1 is greater than the average of int1. Note that the use of operator |
|
Finds all resources where the value of integer property int1 is greater than the average of int1, regarding the own resource type (in other words: finds all folders where the value of integer property int1 is greater than the average of all folders and analogous for documents and links) |
|
Finds all resources where the display name is equal to the maximum display name of either folder, document or link |
|
Finds all resources where the display name is equal to the maximum display name, regarding the own resource type. The difference to the former statement is as follows: assuming, the maximum name of all folders is x, the maximum name of all documents is y and the maximum name of all links is z, then the former statement would also find documents with name x and links with name x and y. In contrast, this statement binds the resource type and therefore only folder x, document y and link z are found |
|
This is an example for a subselect with more than one filter facet property; the subselect finds the maximum display name per resource type and year |
|
Returns yes for all resources where the value of int1 is greater than the average of int1, else no is returned |
|
When searching for versions this query filters the maximum version per resource |
15. Bulk Operations
Writing operations are not supported by NQL which means there is no equivalent to SQL’s insert
, update
or delete
statements. However, the nscale Query Tester does support bulk operations. For the sake of completeness, the syntax of these bulk operations
is also described here, but be aware that this is not part of NQL!
15.1. Bulk Update
The nscale Query Tester supports bulk updates. A bulk update statement consists of the same components as an NQL statement except that the select clause is replaced by an update clause. The update clause consists of the key word update
followed by
a comma-separated list of key-value pairs, where the key is a property and the value is a QueryOperand (a constant, context variable, property, query function or a list of these), separated by the =
character.
15.1.1. Examples
Update statement | Description |
---|---|
|
Updates property displayname for all resources in current folder where displayname is null |
|
Updates property displayname for all resources in subtree of current folder where displayname is null |
|
Updates properties int1 and int2 where the sum of int1 and int2 is less than 3 |
|
Updates property filename for all resources having content with the value of property displayname, a dot and the value of property fileextension |
15.2. Bulk Delete
The nscale Query Tester supports bulk deletes. A bulk delete statement consists of the same components as an NQL statement except that the select clause is replaced by a delete clause. The delete clause simply consists of the key word delete
.
15.2.1. Examples
Delete statement | Description |
---|---|
|
Deletes all resources in current folder where displayname equals xyz |
|
Deletes all documents |
|
Deletes all resources |