Relational Queries

SQL Statement Grammar

The following section presents the SQL statement grammar. In some cases, a link is provided to information in this document that is relevant to the use of a category or keyword.

<query> ::= <SQL query> [ OPTIONS '(' <option> { ',' <option> } ')' ]
     

<option> ::= ( TIMELIMIT | FULLTEXTROWLIMIT | COUNT_LIMIT | <optimization> ) <integer>

<optimization> ::= CBR_CONTENT_FIRST | CBR_DB_FIRST | CBR_DYNAMIC_THRESHOLD

Only CBR_DYNAMIC_THRESHOLD requires an integer.

<SQL query> ::= SELECT [DISTINCT| ALL] [TOP<integer>]
                                <select_list>
                               FROM <class_reference>
                               [WHERE <search_condition>]
                               [ORDER BY <orderby> { ',' <orderby> } ]

<select_list> ::= <select_prop> { ',' <select_prop> }{ ',' <custom_function> }

<select_prop> ::= (<property_spec> [ [AS] <entity_name> ] )
                  | ( <class_alias> '.' '*' )
                  | '*'

<class_reference> ::= <from_class> | <qualified_join>

<qualified_join> ::= <joined_class> ( INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] )
                    JOIN <from_class> ON <comparison_predicate>
 

<joined_class> ::= <from_class> | '(' <qualified_join> ')'

<from_class> ::= <class_ident> [ [AS] <class_alias> ] [ WITH (INCLUDESUBCLASSES | EXCLUDESUBCLASSES ) [',' ALLACCESSGRANTED]]

<search_condition> ::= <basic_search_condition> [ ( AND | OR ) <search_condition> ]
 

<basic_search_condition > ::= ( [NOT] '(' <search_condition> ')' ) | <predicate>

<predicate> ::= <comparison_predicate>
                 | <null_test>
                 | <in_test>
                 | <existence_test>
                 | <isclass_test>
                 | <isOfclass_test>
                 | <content_test>
                 | <satisfies_test>
                 | <intersects_test>
 

<comparison_predicate> ::= <scalar_exp> <comparison_op> <scalar_exp> 

<comparison_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | <infolder_op> |             LIKE

<infolder_op> ::= INFOLDER | INSUBFOLDER

<null_test> ::= <property_exp> IS [ NOT ] NULL
 

<in_test> ::= <scalar_exp> IN ( ( '(' <SQL query> | <literal_list> ) ')' ) | <property_spec> )
 

<existence_test> ::= EXISTS '(' <SQL query> ')'

<isclass_test> ::= ISCLASS '(' <class_alias> ',' <class_ident> ')'
 

<isOfclass_test> ::= ISOFCLASS '(' <class_alias> ',' <class_ident> ')'

<content_test> ::= <contains_test> | <freetext_test>

<contains_test> ::= ( CONTAINS ) '(' <content_property_spec>',' <string_literal>[',' <dialect>] ')'

<freetext_test> ::= ( FREETEXT ) '(' <content_property_spec>',' <string_literal> ')'

<content_property_spec> ::= <property_spec> | '(' <class alias> '.' '*')'

<satisfies_test> ::= <property_spec> [AS <class_alias>] SATISFIES '(' <search_condition> ')'

<intersects_test> ::= <list_exp> INTERSECTS <list_exp>

<list_exp> ::= '(' <literal_list> ')'
           | <property_exp>
           | '(' <SQL query> ')'

<scalar_exp> ::= <literal>
                 | <property_exp>
                 | ( '(' <scalar_exp> ')' )
                 | ( <scalar_exp> <arith_op> <scalar_exp> )
                 | <property_spec> [<arith_op> <timespan_exp>]
                 | <now> [<arith_op> <timespan_exp>]

<timespan_exp> ::= timespan [<arith_op> <timespan_exp>]

<now> ::= Now '()'

<arith_op> ::= '+' | '-' | '*' | '/' 

<property_exp> ::= <property_spec> | ( ( UPPER | LOWER  | ABS  ) '(' <scalar_exp> ')' ) | <object_exp>

<object_exp> ::= OBJECT'(' ( <guid> | <string_literal> | <property_spec>) ')' 

<object_exp> ::= SECURITYPRINCIPAL'(' ( <guid> | <string_literal> | <property_spec>) ')' 

<object_exp> ::= CurrentUser'('')' 

<orderby> ::= [ COALESCE '(' <property_spec>, <literal> ')' || <property_spec> ] [ ASC | DESC ]    

<class_ident> ::= <guid> | <entity_name>
     

<class_alias> ::= <integer> | <entity_name>
     

<property_spec> ::= ( <class_alias> '.' <guid> ) | ( [ <class_alias> '.' ] <entity_name> )
     

<entity_name> ::= <simple_name> | <complex_name>
     

In the following rule for <simple_name>, the alpha class of characters includes the underscore (_) character:

<simple_name> ::= <alpha> { <alpha> | <digit> }
     

<complex_name> ::= ('"' { <any_character> } '"' ) | ( '[' { <any_character> } ']' )
     

<literal_list> ::= <literal> { ',' <literal> }
     

<literal> ::= <string_literal> | <integer> | <float> | 
              <ISO datetime> | <W3C datetime> | TRUE | FALSE | UNKNOWN | <guid>
     

<string_literal> ::= <single_quote> {<any_character>} <single_quote>
     

<ISO datetime> ::= YYYYMMDDThhmmss[,ffff]Z
<W3C datetime> ::= YYYY-MM-DD[Thh:mm:ss[.ffff]][<timezone>]
     

<timezone> ::= Z | ( ( '+' | '-') hh:mm )
<guid> ::= '{'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'}'

Class and Property Identification in Query Text

The <class_ident> and <property_spec> production rules include a complexity in the way IBM® FileNet® P8 allows class and property identifiers for queries. The <class_ident> and <property_spec> production rules can be any one of the following rules:

• The <class_ident> production rule can act as the basic identification in the FROM clause. The <class_ident> can be a GUID, the symbolic name, or the display name for a class.

  A <class_ident> can specify only a searchable class. A searchable class is identified by SearchScope.fetchSearchableClassDescriptions. In general, searchable classes are classes that reside in an object store repository (implement the RepositoryObject interface), and are subclasses of IndependentObject.

• A user-defined alias (a number or string) can be associated with the class by using the FROM clause. The user-defined alias can be used to qualify a property identifier or as the first argument to the ISCLASS expression. If an explicit alias is not given, an implicit alias is created according to the following rules:
  • If the <class_ident> takes the form <entity_name>, the name used (whether symbolic or display) becomes the alias.
  • If the <class_ident> takes the <GUID> form, the symbolic name, if supported, becomes the alias. Otherwise, the display name is used.
• A property in the SELECT list can be identified by GUID, symbolic name, or display name. The class qualifier can be omitted if the property name or GUID can be resolved uniquely.
• Properties included in the SELECT statement can be given a string alias (<select_prop>) that can be used elsewhere in the query in place of a GUID or symbolic/display name. Aside from this rule, the same rules apply everywhere that a property identifier appears.
• A property in the top level of the SELECT list (<select_prop>) can be any property that is defined in the class or classes in the FROM clause. No properties in top-level select lists are restricted from being selected, which is a change from prior releases in which many properties were not selectable.
• A property in the <select_list> of a nested SELECT (a SELECT statement inside an IN clause or EXISTS clause) within the SELECT list must be a searchable property. Refer to the property definitions for the properties that are identified as "Searchable". All user-defined properties are searchable, except properties with enum cardinality or binary data type.
• Note that a GUID is not a string. When you use a GUID in a query to identify a specific object, class, or property in a SELECT clause, do not enclose the GUID in quotation marks. For example:

     SELECT DocumentTitle Id FROM Document WHERE Id={4ECDE7D9-F551-4C53-A109-8D81B1DE8577E}

Supported Operators for Property Queries

Properties have a data type that defines the kind of data the property can contain. For example, an integer type property can hold only a value that is an integer. (For more information, see Supported data types.) When you create a query, you can search for property values by data type. Each data type supports a specific set of query operands. The following list identifies the IBM FileNet P8 property data types and the valid operators for each type:

• Binary: Properties of this type are not searchable (that is, cannot be specified in the WHERE clause).
• Boolean: '=' | '<>' |IS[NOT] NULL
• DateTime: '=' | '<>' | '<' | '>' | '<=' | '>=' |IS[NOT] NULL
• Float: '=' | '<>' | '<' | '>' | '<=' | '>=' |IS[NOT] NULL
• ID: '=' | '<>' | '<' | '>' | '<=' | '>=' |IS[NOT] NULL
• Integer: '=' | '<>' '<' | '>' | '<=' | '>=' |IS[NOT] NULL
• Object: '=' | '<>' |IS[NOT] NULL
• String: '=' | '<>' '<' | '>' | '<=' | '>=' | LIKE |IS[NOT] NULL

Folder Operators

The INFOLDER operator matches an object that is contained within a specified folder. The left operand specifies an object-valued property or an expression that results in a single object value. The right operand identifies the folder in one of the following forms:

• The folder ID.
• The full path name of the folder.

If the left operand yields an object of class Folder, and the folder is a direct child of the folder that is identified by the right operand, the expression evaluates to TRUE.

If the left operand yields an object of another containable class (Document, CustomObject), the expression evaluates to true if that object is a referential containee of the folder that is identified by the right operand.

The following example locates all documents that are contained in the /sub1/sub1a folder:

   SELECT … FROM Document WHERE Document.This INFOLDER '/sub1/sub1a'

The INSUBFOLDER operator works in a similar fashion for matching objects that are contained within a folder subtree (a specified folder or any subfolder in that tree, to any depth). The operands for the INSUBFOLDER operator are the same as for INFOLDER.

The following example locates all documents that are contained in the /sub1/sub1a folder or any subfolder:

   SELECT … FROM Document WHERE Document.This INSUBFOLDER '/sub1/sub1a'

INSUBFOLDER can be used only on subfolders that have the Folder.Parent property set to the parent of the folder (direct containment). The INSUBFOLDER operator does not recognize a subfolder that is linked to a parent by referential containment (a ReferentialContainmentRelationship object that has the ReferentialContainmentRelationship.Tail property equal to the subfolder and the ReferentialContainmentRelationship.Head property equal to the parent folder).

IsClass Function

The IsClass function acts as a filter for finding specific classes. In ad hoc queries, the following format is used:

IsClass(<class_alias>,<class_id>)

The first parameter specifies the alias of a FROM class to which the filter is applied. The second parameter specifies the class to be matched; class_id can be either a symbolic name or a GUID.

In a FilterExpression property, the IsClass operator must be constructed as follows: IsClass(Source,<class_id>), where "Source" refers to the source object.

The IsClass function is useful when querying base classes WITH INCLUDESUBCLASSES and the results must include objects that belong to one or more particular subclasses of the base class.

The following example locates documents of either DocSubclass1 or DocSubclass2, but excludes those of class Document or any other subclass:

   SELECT … FROM Document D WHERE IsClass(D,DocSubclass1) OR IsClass(D,DocSubclass2)
  

IsOfClass Function

The IsOfClass function acts as a filter for finding specific classes and their subclasses. In ad hoc queries, the following format is used:

IsOfClass(<class_alias>,<class_id>)

The first parameter specifies the alias of a FROM class to which the filter is applied. The second parameter specifies the class to be matched; class_id can be either a symbolic name or a GUID.

In a FilterExpression property, the IsOfClass operator must be constructed as follows: IsOfClass(Source,<class_id>), where "Source" refers to the source object.

The following ad hoc query example locates accounting records of either PayrollChange or TaxForm, and includes any subclasses of PayrollChange or TaxForm:

SELECT … FROM AccountingRecords a WHERE IsOfClass(a, PayrollChange) OR IsOfClass(a, TaxForm)

The following ad hoc query example locates correspondence records that are not instances of the Email class or any of its subclasses.

SELECT … FROM Correspondence c WHERE NOT(IsOfClass(c, Email))

Include/Exclude Subclasses Function

The INCLUDESUBCLASSES and EXCLUDESUBCLASSES functions, along with IsClass, are used to limit results to specific classes or class hierarchies. INCLUDESUBCLASSES is the default. The following SQL query examples demonstrate these functions.

Both of these queries find documents and their subclasses with the DocumentTitle "MyDoc":

   SELECT … FROM Document WHERE DocumentTitle = 'MyDoc'
   SELECT … FROM Document WITH INCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc'

Both of the next two queries find documents with the DocumentTitle "MyDoc". Matches in subclasses of the Document class are not returned:

   SELECT … FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS (d, Document)
   SELECT … FROM Document WITH EXCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc'

With the following query, the 'Form Data' and 'Form Policy' objects (which are both subclasses of Document) are found:

SELECT … FROM Document d  WHERE … AND (ISCLASS (d, [Form Data]) OR ISCLASS (d, [Form Policy]))

The next query is used to find objects from all subclasses of Document except the Document class itself. (INCLUDESUBCLASSES is used by default.)

SELECT … FROM Document d WHERE … AND NOT (ISCLASS (d, Document))

ALLACCESSGRANTED Function

ALLACCESSGRANTED is an optional function that filters a query by the specified access rights that are granted to the user. The server returns only those objects to which the query user has at least the access rights that are specified in the ALLACCESSGRANTED argument.

The syntax of this function is as follows:

ALLACCESSGRANTED(x)

Where x is a bit mask, an integer from 0 (zero) to Integer.Max_Value. The query user must have at minimum all the bits in the mask.

For example, a query that requires a minimum of CHANGE_STATE and DELETE access rights that are granted to a user on objects that are returned from a search would specify 66560 as the access mask (the sum of the CHANGE_STATE value of 1024 plus the DELETE value of 65536). The client must manually sum the individual access right values.

SELECT … FROM myClass WITH ALLACCESSGRANTED(66560) WHERE …

To determine the integer values for the AccessRight constant, see Constant Field Values.

ALLACCESSGRANTED can be used with the Include/Exclude Subclasses functions, which are separated by a comma. For example:

SELECT … FROM myClass WITH EXCLUDESUBCLASSES, ALLACCESSGRANTED(128)

ALLACCESSGRANTED can be applied to each of the FROM classes in a search. It can be applied to a joined class. Only one ALLACCESSGRANTED can be present in a WITH subclause. For example:

SELECT … FROM myClass d WITH EXCLUDESUBCLASSES, ALLACCESSGRANTED(128)
     INNER JOIN myOtherClass e WITH EXCLUDESUBCLASSES, ALLACCESSGRANTED(129)
     ON d.This = e.This

Limitations

• ALLACCESSGRANTED cannot be present in a subquery.
• ALLACCESSGRANTED cannot be cannot be applied to the ContentSearch class (in a FROM clause). It can be applied to the class that is joined to ContentSearch.

  The following examples show correct and incorrect ALLACCESSGRANTED usage with the ContentSearch class.

  Correct

  SELECT … FROM Document d WITH ALLACCESSGRANTED INNER JOIN ContentSearch WHERE …
              

  Incorrect

  SELECT … FROM Document d INNER JOIN ContentSearch WITH ALLACCESSGRANTED WHERE …
  SELECT … FROM Document d WITH ALLACCESSGRANTED INNER JOIN ContentSearch WITH ALLACCESSGRANTED WHERE …

Object Function

The OBJECT function provides a means to create the effect of an object constant, which can then be compared to an object-valued property or expression. There are two logical forms of the OBJECT function, one of which takes an argument of type GUID (Id), which can either be a constant or a GUID-valued property, and the other form, which takes one of the following string arguments:

• For Folder objects, the full path name of the folder.
• A <property_spec> for a property of data type Id. An OBJECT function can be used to compare objects to IDs, which can be especially useful in joins.

For example, the following statement locates all immediate subfolders of the /root/sub1/sub1a folder:

   SELECT … FROM Folder WHERE Parent = OBJECT('/root/sub1/sub1a')

SecurityPrincipal Function

The SecurityPrincipal function provides a means to construct a search query that provides a comparison with a user or group security principal ID. This function operates similarly to the Object function, but is used exclusively for SecurityPrincipal objects (User or Group objects). In ad hoc queries and FilterExpression properties, the following format is used:

SecurityPrincipal(<security_principal_id>)

This function takes a security principal string ID parameter or any parameter that is supported by the Factory.SecurityPrincipal.fetchInstance() method. The following example locates all documents that were created by a particular user:

   SELECT … FROM Document WHERE Approver = SecurityPrincipal('S-1-2-567-8910111213') 

CurrentUser Function

The CurrentUser function provides a means to construct a search query that provides a comparison with the current logged-in user. In ad hoc queries and FilterExpression properties, the following format is used:

CurrentUser()

This function takes no parameters. The following example locates all documents that are created by the current user:

   SELECT … FROM Document WHERE Approver = CurrentUser() 

Comparing Objects

When <comparison_predicate> is used to compare two object-valued properties, the IDs of the objects, rather than the contents, are being compared. For this reason, comparing objects is as fast as comparing IDs.

Pattern-matching Queries on String Properties

Using the LIKE operator, you can perform pattern-matching queries (also called wildcard queries) on single-valued and multi-valued string properties. The following example shows a wildcard query on a string property:

WHERE DocumentTitle LIKE 'hello%'

You can also perform wildcard queries on multi-valued string properties, as shown in the following example:

   WHERE mv_string_prop LIKE 'he%'

Where mv_string_prop has cardinality of LIST.

Using multi-valued properties as the left operand for other query operations is not allowed. The following example causes the query to fail:

   WHERE mv_string_prop = 'abc'

IN Operator

The following formats are used for the IN operator. Unless otherwise indicated, all formats are applicable to both ad hoc queries and the FilterExpression property.

• value IN listproperty

  Where "value" is either a property name or a constant (<property_spec> | <literal>), and listproperty is a property name (<property_spec>) of a property that has cardinality of LIST. This format is used to search a property that is of cardinality LIST. If the list has a value equal to "value", the expression is true. You cannot use "value = listproperty" to compare a property of type LIST to a value; you must use the IN operator.

  Note: For queries that contain two or more multivalued property conditions, do not use the IN operator in a succession of OR clauses. For information, see Use INTERSECTS Operator for queries with multivalued properties.
• value IN (constant1, constant2, constant3,…)

  Where constant1, constant2, constant3 are constants (<literal>s). This format is used to determine if "value" is equal to one of the constants in the list. When multiple items are between the parentheses, the items must be constants.

• value IN (SELECT someproperty FROM …)

  Not applicable to FilterExpression property.

  This format is used to check if "value" is one of the results that are returned by the subquery. The subquery must select only one property.

• <object_exp> IN <enum_property>

  Applicable only to FilterExpression property.

  For example, the following filter expression in a sweep job or sweep policy would match any document that was filed in the folder with Id equal to {0F1E2D3C-4B5A-6978-8796-A5B4C3D2E1F0}.

  Object({0F1E2D3C-4B5A-6978-8796-A5B4C3D2E1F0}) IN FoldersFiledIn

SATISFIES Operator

The SATISFIES operator can be used to express an existence constraint on dependents of the searched objects. The following format is used for the SATISFIES operator:

<dependent list prop> [AS <class_alias>] SATISFIES ( <predicate> )

This evaluates to TRUE if any member of the list satisfies the predicate. The predicate can include anything that is valid in a WHERE expression; any unqualified property names in the predicate are resolved against the required class of the list property or any subclass thereof (implicit include-subclasses behavior). Qualified names can be used to do a correlated subquery.

Example:

SELECT … FROM Document WHERE ExternalReplicaIdentities SATISFIES (ExternalRepository = Object({ba950301-afc2-4fd3-bbb9-dc91663cc15a}))

For ad hoc queries, the following rules apply:

• The property that is named on the left side of the expression must be a dependent list of object property whose PropertyDescription has the value TRUE for the IsSearchable property. This includes most dependent list of object properties, but excludes ContentElements and Permissions, whose storage representation precludes queryability. (A list of object property is of data type Object and of cardinality List. All list of object properties are system-defined. Custom list properties can be only of data types other than Object.)
• The predicate on the right side can mention only dependent object properties that are persistent, and is indicated by the IsSearchable property of their PropertyDescriptions object that has a value of TRUE.

For the use of SATISFIES in a FilterExpression property, there are exceptions to these rules.

SATISFIES expressions can be nested to allow constraints on dependents of dependents, for example, the ExternalPropertyAliases of a PropertyDefinition of a ClassDefinition.

The optional AS clause can be used to give an alias for the implicitly queried required class, for use in an IsClass clause or to disambiguate when nested SATISFIES clauses are used.

Example:

SELECT … FROM ClassDefinition WHERE PropertyDefinitions AS pd SATISFIES 
  (IsClass(pd,PropertyDefinitionString))

FilterExpression Exceptions

SATISFIES is supported in a FilterExpression property with the same syntax as an ad hoc query, with these exceptions:

• The persistency limitations on the list property and the properties of the dependent class do not apply, allowing any list property (including ContentElements and Permissions), and any property, whether persistent or not, of the dependent class to be named in the expression.
• A singleton or enumeration property can be named on the left side.

Example:

// applied to an Annotation object
AnnotatedObject SATISFIES (VersionStatus=3)

// applied to a Document
FoldersFiledIn SATISIFES (This = Object({9906D34A-DBE2-415E-B807-6B4CCA65B893}))

INTERSECTS Operator

The INTERSECTS operator can be used to test if a list property contains any member of a set of values, or has values in common with another list property or with the return values of a subquery.

The following format is used for the INTERSECTS operator:

<list 1> INTERSECTS <list 2>

The expression evaluates to TRUE if and only if the intersection between the set of values in list 1 and those in list 2 is non-empty; that is, there is at least one value in common between the two sets. The lists must be the same data type.

The INTERSECTS operator supports a constant list and a list property name on either the right or left side, and list properties on both sides. For ad hoc queries only, a list expression (sub-SELECT) is also supported.

Example:

SELECT … FROM Document WHERE ListPropertyString INTERSECTS ('value1', 'value2', 'value3')

SELECT … FROM Document WHERE ListPropertyString1 INTERSECTS ListPropertyString2

The following form is supported only in ad hoc queries (not in the FilterExpression property):

SELECT … FROM Document WHERE ListPropertyString INTERSECTS (SELECT documentTitle FROM Document …)

List Property with IS/IS NOT NULL

With the IS NULL or IS NOT NULL operator, you can test whether a list property contains any values.

Example:

This query returns documents in which there are no values set for ListPropertyString.

SELECT … FROM Document WHERE ListPropertyString IS NULL

This query returns documents where ListPropertyString contains values.

SELECT … FROM Document WHERE ListPropertyString IS NOT NULL

Time-related Functions

As described below, the Now function and the TimeSpan function are used in expressions with datetime properties. A datetime property is either a user-property or a searchable system datetime property, such as DateCreated and DateLastModified. These functions are especially useful in targeting audited events for deletion that is based on age, as defined in the DispositionRule property of an CmAuditDispositionPolicy object.

The following rules apply:

• All operators that are supported for datetime comparisons are supported by Now and TimeSpan.
• The functions can be used together in the same expression.
• The functions are case insensitive.

Now

The Now function returns the current Coordinated Universal Time (UTC) datetime, based on the database system timestamp derived from the operating system of the computer on which the database is running. The following format is used:

Now()

Example:

This query returns all documents where the values of DateProp are greater than the current UTC time on the database server.

SELECT … FROM Document WHERE DateProp > Now()

TimeSpan

The TimeSpan function allows durations between datetime values to be evaluated. The following format is used:

TimeSpan(value, 'units')

where:

value is any positive integer value, allowed by the range for its units.

The argument is case insensitive.

Example:

This query returns all documents where DateProp1 is greater than DateProp2 plus one hour.

SELECT … FROM Document WHERE DateProp1 > DateProp2 + TimeSpan(1, 'hour')

This query returns all documents where DateProp1 less one hour is greater than DateProp2 plus 71 hours.

SELECT … FROM Document WHERE DateProp1 - TimeSpan(1,'hour') > DateProp2 + 
TimeSpan(3, 'days') - TimeSpan(1, 'hour')

This query returns all documents added in the last three days.

 SELECT … FROM Document WHERE DateCreated > Now() - TimeSpan(3, 'Days')

General Restrictions

The following restrictions are imposed by general database rules:

• A TimeSpan function can be only added to or subtracted from a datetime property or a Now function.
• A TimeSpan function can exist only on the right side of an expression.
• A TimeSpan function cannot exist in a separately parenthesized expression.
• Other constructs, such as database functions or differencing, cannot be called in the select list.

The table shows examples of invalid and valid constructs:

Float-Valued Property Queries

When performing comparison queries on float-valued properties, you must use a decimal point in the value. The following examples use the Document.ContentSize, a float-valued property, to illustrate incorrect and correct syntax for a comparison query.

This query produces an error:

   SELECT … FROM Document WHERE ContentSize > 3

The correct form for this query is:

   SELECT … FROM Document WHERE ContentSize > 3.0
  

Joins

A JOIN is used to query the result from multiple tables, and behave the same as ANSI/ISO SQL99 compliant joins. The syntax is slightly more restrictive, however, and requires parentheses if multiple joins are used. For example, the following statement uses two joins:

   SELECT … FROM (class1 INNER JOIN class2 ON class1.x = class2.y) INNER JOIN class3 
            ON class3.x = class1.y

Note the parentheses around "class1 INNER JOIN class2 ON class1.x = class2.y". Additionally, the ON clause must immediately follow the class of the JOIN, and precede the next JOIN clause. This is more restrictive than ANSI/ISO SQL99.

See also CBR Queries.

COALESCE Function

The COALESCE function can be used in an ORDER BY clause to control the ordering of properties that contain null values. The function substitutes a specified literal value for a null value during the ordering of the returned result set. Without the COALESCE function, properties with null values are ordered first or last depending on the database vendor. With the COALESCE function, properties with null values are ordered as you want. The order is specified by choosing the literal value that nulls will be converted to in the COALESCE function.

The syntax of this option is as follows:

COALESCE (myProperty, value)

Where:

myProperty is an orderable and searchable property. The ORDER BY restrictions apply to the COALESCE function.

value is a literal value of the same data type as the property.

In the following search example, for every ProcessDate property in the result set that contains null, the COALESCE function replaces the null value with the 1900 date value. With descending order specified, rows with nulls are returned last because the 1900 substitution date is the oldest date in the range of possible dates for the database.

SELECT … FROM … WHERE … ORDER BY COALESCE(d.ProcessDate, 19000101T000000Z) DESC

You can use the COALESCE function with multiple properties in the ORDER BY clause, as shown in the following example.

SELECT … FROM … WHERE … ORDER BY COALESCE(d.ProcessDate, 19000101T000000Z) DESC, COALESCE(d.ProcessClerk, 'zzUnknown') ASC

The COALESCE function is subject to the following restrictions and rules:

• The function is not supported in WHERE or Select clauses
• You cannot coalesce a property into a literal data type that is disallowed, for example, a float or integer property into a string value, or a string property into an integer or float value.
• Distinct searches return rows distinct on their coalesced order by values. The selected value that is shown in results, however, is the original value, not the coalesced value.
• GUID's that are coalesced require curly brace form, for example, COALESCE(d.Id, {00000000-0000-0000-0000-000000000000}).
• Strings that are coalesced require single quotation marks, for example, COALESCE (myProperty, 'myDefaultValue').

Query Restrictions

Certain restrictions exist when you create and run queries.

Long String Column Operations

You can define a custom string property to store in either a short or long database column by setting the UsesLongColumn property when the property is created. A long string column can appear only in a WHERE clause of a query as part of a LIKE, IS NULL or IS NOT NULL expression or, if enabled for CBR, as part of a CONTAINS expression.

ORDER BY restrictions

Properties of type Boolean, DateTime, Float64, ID, Integer32, and Object may appear in an ORDER BY clause, along with short String properties. Neither Binary nor long String properties may be used to order a query.

Any property appearing in the ORDER BY clause must also be present (explicitly or by use of *) in the SELECT list.

DISTINCT restrictions

A DISTINCT query can be performed only when all of the SELECTed properties are orderable. For example, if property P1 is not orderable (Binary type, or String type with UsesLongColumn), the following query produces an error message:

SELECT DISTINCT P1 From Object1

Query Options

Timelimit

The query time limit for a client-to-server RPC, in seconds. The syntax of this option is as follows:

OPTIONS (TIMELIMIT N)

where N can be an integer from 0 (zero) to Integer.Max_Value, up to the maximum specified in the ObjectStore.MaxQueryTimeLimit property.

If this option is not set, the effective limit is the minimum of the configured DefaultQueryTimeLimit and MaxQueryTimeLimit properties set on the ObjectStore. If neither property is set, the query time is unlimited.

The QueryDatabaseTimeout property can also be set as an additional time limit applied only to the JDBC statement execution against the database during an ad hoc search.

For more information, see Server Query-Timeout Behavior.

Count_Limit

Requests that the Content Engine count the number of search results up to a specified limit for a paged search. (This option is ignored for non-paged searches.) In response, the Content Engine returns an integer that indicates one of the following meanings:

• A minimum of N rows were found, where N is equal to the COUNT_LIMIT setting. In other words, there were more than N rows found, but the Content Engine counted up to N rows only.
• Exactly R rows were found, where R is less than the COUNT_LIMIT setting.
• No result count because counting is disabled on the server.

To get the result count, the client application must call PageIterator.getTotalCount from the Content Engine API. The PageIterator can be retrieved from the object set representing the objects or rows that are returned by the search.

The syntax of this option is as follows:

OPTIONS (COUNT_LIMIT N)

Where N can be an integer from 0 (zero) to Integer.Max_Value, up to the maximum specified in the ServerCacheConfiguration.QueryCountMaxSize property. If the option is set to zero, then the Content Engine counts the number of rows as set in the ServerCacheConfiguration.QueryCountDefaultSize property.

In the following example, the search returns the count that is found, up to 1000 rows.

Select Id, DocumentTitle From Document
Where DocumentTitle LIKE 'Specification%'
OPTIONS (COUNT_LIMIT 1000)

This search returns the count found up to the server default value of the QueryCountDefaultSize property.

Select Id, DocumentTitle From Document
Where DocumentTitle LIKE 'Specification%'
OPTIONS (COUNT_LIMIT 0)
    

The following table describes the Content Engine behavior in response to different setting combinations for the SQL COUNT_LIMIT option, the QueryCountDefaultSize property, and the QueryCountMaxSize property.

To optimize result count performance, the administrator must review query time limit settings on the server. For more information, see DefaultQueryTimeLimit, MaxQueryTimeLimit, and QueryDatabaseTimeout.

Background Search Query Syntax

SELECT DocumentTitle FROM Document1 WHERE DateCreated > @StartDate@

If the selected property has the same name as a property in the properties collection, the property can be directly selected by name in the search expression. If the names do not match (or cannot be matched), an AS clause can be used in the SELECT list of the query to map the selected property to a specified destination property. If no mapping can be found for a selected property, that value is ignored. Background search supports the following data coercions for mapping properties:

• Integer to String
• Float to String
• Boolean to String
• DateTime to String
• ID to String
• Object to String (string form of object identity)
• Binary to String (hex-encoded)
• Integer to Float
• Boolean to Integer (0 = False, 1 = True)
• Object to ID (object identity)

The following system properties that are populated in a CmAbstractSearchResult result object cannot be matched by symbolic name; they must be mapped by using an AS clause:

• BackgroundSearch
• Creator
• DateCreated
• DateLastModified
• Id
• LastModifier
• Owner
• This

SELECT DateCreated AS DocCreationDate, FROM Document1 WHERE …

Aggregate Functions and the GROUP BY Clause

For background searches only, Content Engine supports SQL aggregate functions and the GROUP BY clause. SQL aggregate functions perform a calculation on a set of values and return a single value that is a result of the calculation. The following aggregate functions are supported:

• COUNT(): Returns the number of items in the expression.
• MAX(): Returns the largest value in the expression.
• MIN(): Returns the smallest value in the expression.
• SUM(): Returns the sum of the values in the expression.

SELECT Creator as DocCreator,
    COUNT(Id) as DocCount,
    SUM(ContentSize) as DocSize,
    MIN(DateCreated) as DateFirstCreated,
    MAX(DateCreated) as DateLastCreated
FROM Document
GROUP BY DocCreator

For more information about background searches, see Background Searches

Custom Search Function Query Syntax

SELECT DocumentTitle, Id, SCF::DateConversionFunctionHandler(DateCreated, 'Month') as MonthYear FROM Document

For more information about custom search functions, see Custom Search Functions.

General Query Examples

The following section provides examples of simple SQL statements to retrieve various pieces of information.

Retrieve documents that are created by the Administrator user after April 1, 2005, and return the specified list of properties for each returned document:

   SELECT Creator, DateCreated, DocumentTitle FROM Document 
            WHERE (DateCreated >20050401T080000Z AND Creator='Administrator')

Retrieve documents by ID (GUID) that have a document title that contains the character pattern "Acct":

   SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE '%Acct%'

If the object store contains three documents whose titles contain the characters "Acct", then this query would return them in this format:

AcctDocument      {4E017CB8-4980-4BB2-88E9-248C555445E2}
MyAcctDocument    {01C92932-E840-4FC2-90E8-45E245248CB5}
AcctgStoredSearch {4ECDE7D9-F551-4C53-A109-8D81B1DE8577}

To search for a specific ID, you can change the query to specify a particular GUID:

   SELECT DocumentTitle Id FROM Document WHERE Id={4ECDE7D9-F551-4C53-A109-8D81B1DE8577E}

The following query retrieves the list of checked out documents (document versions in Reservation state) created by the HRManager user:

   SELECT Id FROM Document WHERE VersionStatus=3 AND Creator='HRManager'

By changing the value of the VersionStatus property, you can use this query to search for documents that are Released (1), In Process (2), or Superseded (4).

The following SQL queries retrieve the containees from a particular folder (in this example, the folder /Test). Using such queries, you can set the criteria to limit the number of items that are returned to only those items that are required by your application. In contrast, the Folder.getContainees method returns all items in a folder, with no limit on the number of items returned.

Get subfolders:

   SELECT f.[ObjectType], f.[Id], f.[FolderName], f.[ContainerType], f.[ClassDescription], 
            f.[OIID]  FROM Folder f 
            WHERE (( f.This infolder '/Test')) AND (( [IsHiddenContainer]=false) )) 
            ORDER BY FolderName

Get documents:

   SELECT d.[ObjectType], d.[Id], d.[LastModifier], d.[DocumentTitle], d.[DateLastModified], d.[ContentSize], 
            d.[MajorVersionNumber], d.[ClassDescription], FROM Document d 
            WHERE (( (d.This infolder '/Test') ) AND (( [MimeType] IS NULL) 
                OR ([MimeType] <> 'application/x-filenet-search') ) )) 
            ORDER BY DocumentTitle

The following sample query searches for and returns the ID of all HTML documents in the database with greater than 10,000 bytes of content, and that have been modified since December 12, 2005:

   SELECT Id FROM Document d WHERE ([MimeType] = 'text/html' AND [ContentSize] > 10000.0) 
            AND [DateLastModified] > 20051201T000000Z