SQL Syntax Reference

The FileNet P8 Platform supports a query syntax that conforms generally to SQL-92 and specifically is aligned with SQL Server query syntax wherever there is equivalence of function, with classes and properties playing the role of tables and columns. This section:

The statements and descriptions in this topic follow these conventions:

Convention Description
UPPER CASE bold type
Keyword tokens. For example, SELECT.
Single quotes
'
Surround symbols forming part of the grammar. For example, '*'.
Square brackets
[ ]
Surrounding a token or series of tokens indicates an optional clause. For example, [ORDER BY <orderby>]. (Also, see the Note below about using square brackets with SQL reserved words.)
Brackets
{ }
Surrounding a token or series of tokens indicates an optional clause that may be repeated more than once.
Single vertical lines
|
Separate alternatives. For example, AND | OR.
Parentheses without accompanying quotes
( )
Determine precedence. For example, (AND | OR).
Italic type
<italic>
Indicates production rules whose definitions are well known. For example <integer>.

Keyword matching is case-insensitive, even though keywords appear in upper case (see examples below).

Note If a class has a property of the same name as a reserved SQL word, and you want to search for that property, you should surround the property name in your SELECT statement with brackets ([ ]) to prevent errors. (For a list of reserved words, refer to your SQL documentation.) For example, if your Document class has a property named "From", which is a reserved word in SQL, structure your SELECT statement in the following manner:

  SELECT d.[DocumentTitle], d.[From], d.[Id] FROM Document d WHERE d.[Creator] = "jsmith"

SQL Statement Grammar

This 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> ::= ( BATCHSIZE | TIMELIMIT | FULLTEXTROWLIMIT) <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> }

<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 )]
<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>
                 | <content_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> ')'

<content_test> ::= ( CONTAINS | FREETEXT ) '(' <property_spec>',' <string_literal> ')'

<scalar_exp> ::= <literal>
                 | <property_exp>
                 | ( '(' <scalar_exp> ')' )
                 | ( <scalar_exp> <arith_op> <scalar_exp> )
<arith_op> ::= '+' | '-' | '*' | '/' 
<property_exp> ::= <property_spec> | ( ( UPPER | LOWER  | ABS  ) '(' <scalar_exp> ')' ) | <object_exp>
<object_exp> ::= OBJECT'(' ( <guid> | <string_literal> | <property_spec>) ')' 
<orderby> ::= <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>
<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>

Note Include a single quote as part of the literal value by using two consecutive single quotes. For example, 'document''1234'.

<ISO datetime> ::= YYYYMMDDThhmmss[,ffff]Z

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

Note If you omit the time portion (Thh:mm:ss), 00:00:00 is assumed. If you omit the <timezone> option, Z is assumed, which is Coordinated Universal Time (UTC).

<timezone> ::= Z | ( ( '+' | '-') hh:mm )

<guid> ::= [<single_quote>] '{'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'}' [<single_quote>]

Class and Property Identification in Query Text

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

Folder Operators Description

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

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

If the left-hand 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 identified by the right-hand operand.

The following example locates all documents 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 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 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 will not recognize a subfolder linked to a parent by referential containment (a ReferentialContainmentRelationship object having the ReferentialContainmentRelationship.Tail property equal to the subfolder and the ReferentialContainmentRelationship.Head property equal to the parent folder).

Note: Use of the INSUBFOLDER operator results in a complex query that can take a long time to execute. Before deploying a solution that uses an INSUBFOLDER search across a large number of folders and containees, be sure to check that this search behaves well on a large database, and is properly optimized. The database is not capable of properly optimizing all queries.

IsClass Function Description

The IsClass function acts as a filter for finding specific classes. 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 first parameter specifies the alias of a FROM class to which the filter is applied and the second specifies the class which it must match.

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)

Include/Exclude Subclasses Function Description

The INCLUDESUBCLASSES and EXCLUDESUBCLASSES operators, along with ISCLASS, are used to limit results to specific classes or class hierarchies. INCLUDESUBCLASSES is the default. SQL query examples are shown below.

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, we want to find 'Form Data' and 'Form Policy' objects (which are both Document subclasses):

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

We can use the next query to find objects from all subclasses of Document except the Document class itself. (Remember that INCLUDESUBCLASSES is used by default.)

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

Object Function Description

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 which takes a string argument, which can be one of the following:

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

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

Comparing Objects

When <comparison_predicate> is used to compare two object-valued properties, the IDs of the objects, rather than the contents, are actually what is being compared. For this reason, comparing objects is just 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- 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-hand operand for other query operations is illegal. The following example would result in the query failing:

   WHERE mv string prop = 'abc'

The IN Operator

The following formats are used for the IN operator:

  1. 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. Note that you cannot use "value = listproperty" to compare a property of type LIST to a value; the IN operator must be used.

  2. 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. Note that when multiple items are between the parentheses, the items must be constants.

  3. value IN (SELECT someproperty FROM …)

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

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 will produce an error:

   SELECT … FROM Document WHERE ContentSize > 3

The correct form for this query is:

   SELECT … FROM Document WHERE ContentSize > 3.0

Joins

Joins are 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 parenthesis if multiple joins are used. For example, the following is a statement using 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. Note that this is more restrictive than ANSI/ISO SQL99.

See also Full-Text_Joins

Full-Text Queries

Full-text queries (also called CBR queries for "Content Based Retrieval") use the CONTAINS and FREETEXT functions to filter text. These functions allow searching for a word or phrase in document or annotation content, or in string properties.

Only one CONTAINS or FREETEXT clause is allowed per search statement.

Full-text queries can take a considerable amount of time to execute. Some queries can finish in a few seconds, while others could potentially run for hours. Your applications should be written to allow the user to set a timeout; a single default value is probably not sufficient. The user setting(s) should ensure that either the query does not run longer than desired, or that the timeout value is high enough to enable the query to finish execution. Note that the timeout value is the time required to fetch a page for a continuable query, not the time to fetch all pages for the query.

Full-text queries can return matches on more than one content element of a document, or on the document and CBR-enabled string properties of a document. In this case, duplicate rows for that document may be returned in the query results, with a maximum of one row returned for each content element plus one more row for all CBR-enabled string properties.

Long full-text queries that require fetching several pages of data should not be executed while objects matching the full-text query are being either deleted or modified; data may be missing from the results. For instance, suppose a full-text query was started and the first page was fetched, then some rows matching the CONTAINS or FREETEXT clause were deleted. Subsequently, when the second page is fetched, the second page may not contain all existing rows that would be expected. Note that the missing rows are not the ones deleted (matching the CONTAINS or FREETEXT clause), but are existing database rows that are skipped because the starting point for the second page of data may not be valid.

See also FullTextRowLimit and Optimized Queries.

The CONTAINS Function

Use CONTAINS to search for precise or fuzzy (less precise) matches for the following types of text:

The first parameter in the CONTAINS function identifies the property or pseudo-property to which the content filter is to be applied, and the second parameter specifies the filter expression.

The first parameter (<property_spec>) can be any of the following:

Note: Support for the first parameter as a property name (anything other than "Content" or "*") was implemented in the 4.0 release. If you have full-text indexing information that was created using a prior release, this information is not associated with a property: using a property name in the CONTAINS clause will not return any matches based on the full-text information from the prior release. If the functionality of searching a specific property is required, the documents from the prior release can be re-indexed using an index job (IndexJob class) to create the necessary property-related full-text index.

The second parameter of the CONTAINS function is the filter expression, and indicates what to search for. Refer to the Autonomy K2 documentation for more information about the format and use of filter expressions for CONTAINS. Autonomy has this documentation in their Verity K2 product under the heading "simple parser".

The FREETEXT Function

Use FREETEXT to search the text of document content for values that match the meaning, but not the exact wording in the filter expression. When using FREETEXT, the full-text query engine internally breaks the filter expression into a number of search terms, assigns each term a weight, then finds the matches.

The first parameter of the FREETEXT function (<property_spec>) must be either "*" or "Content" to denote searching all CBR-enabled data, and the second specifies the filter expression. The literal "Content" is supported for backward compatibility and is equivalent to "*". "Content" is deprecated and should not be used for new applications.

The second parameter of the FREETEXT function is the filter expression, indicating what is being searched for. See the Autonomy K2 documentation for more information about the format and use of FREETEXT filter expressions. Autonomy has this documentation in their Verity K2 product under the heading "freetext parser" and "query by example (QBE) parser". The QBE parser is equivalent to the freetext parser.

Note that the FREETEXT function does not support searching a single string property that is CBR-enabled, because that support does not exist in Autonomy K2. All CBR-enabled properties (including content, if any), are searched by an instance of FREETEXT. Use the CONTAINS function instead of FREETEXT to search a single CBR-enabled string property without searching other properties or content (note that the filter expression is somewhat different).

Full-Text Joins

Whenever a CONTAINS or FREETEXT clause is used, a join is performed on the (internal) ContentSearch or VerityContentSearch classes.

Note: Instances of the ContentSearch or VerityContentSearch classes cannot be retrieved. However, values for the properties of these classes can be specified, and are defined in the metadata for these classes. See ContentSearch and VerityContentSearch.

The join is necessary, because when a query with a full-text search is executed, the full-text search is done first, then the data from the full-text search is copied to a temporary database table that is referenced by either the ContentSearch or VerityContentSearch class name (both behave identically). The remainder of the search statement is then executed against the repository, joining to this temporary table to access the full-text search data.

ContentSearch is a base class, and VerityContentSearch is a subclass of ContentSearch. VerityContentSearch has properties that are specific to the Verity full-text search engine. When other full-text search engines are supported, other subclasses of ContentSearch will be used to access data specific to the particular full-text engine being used.

Only one join to the VerityContentSearch or ContentSearch class is allowed per query statement, since only one CONTAINS or FREETEXT clause is allowed.

The following is an example of an inner join in a full-text search:

   SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CONTAINS(*,'text') AND D.Creator = 'Frank'

This query is executed by first evaluating the CONTAINS(*, 'text') clause and writing that data to a temporary table. Then the remainder of the query is executed:

   SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CS.QueriedObject IS NOT NULL AND D.Creator = 'Frank'

Note that the CONTAINS clause has been replaced by CS.QueriedObject IS NOT NULL.

In the example above, the CONTAINS clause is used with an AND operator. An OR operator would yield confusing results due to the presence of the inner join (which is an effective AND). For this reason, an AND operator is required with an INNER JOIN, and an OR operator is disallowed.

An example of an outer join would be:

   SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CONTAINS(*,'text') OR D.Creator = 'Frank'

The outer join above first executes the CONTAINS clause and builds the temporary table in the database that holds the full-text search results, then executes the query:

   SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject 
            WHERE CS.QueriedObject IS NOT NULL OR D.Creator = 'Frank'

When using outer joins, only the ContentSearch class can be joined conditionally. The query above would not be allowed to use a right outer join, because that would potentially return only ContentSearch data, and that is not allowed. Outer joins must also use an OR operator when combining the CONTAINS clause with other conditions. Use of an AND operator here is disallowed.

Query Restrictions

Certain restrictions exist when performing queries, as noted below.

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 may only appear in the 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 or FREETEXT 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 only be performed when all of the SELECTed properties are orderable (see above). For example, if property Foo is not orderable (Binary, or String type with UsesLongColumn), the following query produces an error message:

SELECT DISTINCT Foo From Bar

Query Options

Batchsize

Obsolete (not used)

Timelimit

The time limit for the query, in seconds.

FullTextRowLimit

FullTextRowLimit indicates the number of rows to pull from the full text index prior to executing the remainder of the query. There is one row for each content element that is indexable, plus one more row for all CBR-enabled strings of an object. As noted in Full Text Joins, data from the full text index is copied to a temporary table, and that table is then joined with the remainder of the object store database to execute the query.

A query may have a CONTAINS clause that matches many thousands of rows in the full-text index. However, a user may not want to pull all these rows into the temporary database table prior to running the remainder of the query. In this case, the FullTextRowLimit can be set to a lesser value, enabling the user to see a subset of the matches more quickly.

You must be careful not to set the FullTextRowLimit too low. For example, suppose a query has the WHERE clause "WHERE color = 'red' and CONTAINS(*, 'blue')". This query may have a thousand rows that match "CONTAINS(*, 'blue')", but only ten rows that match "color = 'red'" and "CONTAINS(*, 'blue')". If FullTextRowLimit is set to 500, then only 500 rows are pulled from the full-text index and written to the temporary table. However, those 500 rows may not be the same rows that also have "color = 'red'" in the database, so not all ten rows may be found as a result of the query.

If a value for FullTextRowLimit is not supplied, the value of the ObjectStore.FullTextRowDefault property (stored in the GCD) is used.

If a value for FullTextRowLimit is supplied and this value is greater than the value of ObjectStore.FullTextRowMax, then the value of ObjectStore.FullTextRowMax is used instead. The FullTextRowMax property is present on the ObjectStore class, enabling the system administrator to prevent queries from using too much processing time.

FullTextRowLimit and Optimized Queries

A full-text query is optimized if the following is true:

An optimized full-text query can return results faster in some instances, and will also allow the user to retrieve more rows without hitting the FullTextRowLimit value, as well as possible timeout errors or out of memory conditions.

If a full-text query is optimized, when a request for the first page is generated, the server retrieves the FullTextRowLimit number of rows from the full-text search engine and stores them in the temporary database table, then executes the relational part of the query. However, if an insufficient number of rows are found to fill the current page, the server retrieves the next set of rows from the full-text search engine, repeating the process until enough rows are found. When it pulls the second and subsequent set of rows from the full-text search engine, the server has the percentage of rows retrieved from the full-text engine that are actually used in the resultant data to be returned to the user, so it no longer uses the FullTextRowLimit, and instead retrieves the number of rows based on the prior percentage used.

When the second or subsequent page is requested for an optimized full-text query, the server also retrieves the next set of rows from the full-text search engine. Therefore, this type of query can be used to browse through an unlimited number of rows that match a content search.

Optimization may not be suitable for all types of queries: ordering by descending VerityContentSearch.Rank does slow the relation part of the query, and some callers may not want the data to be returned in descending order.

A continuable query that is not optimized will not continue past the FullTextRowLimit number of rows. When the query is not ordered by descending VerityContentSearch.Rank, the server cannot retrieve the next set of rows for each subsequent page (these rows can only be retrieved from the search engine by descending VerityContentSearch.Rank). Instead, the server retrieves the same set of rows for each page processed, using the FullTextRowLimit value for the number of rows.

FullTextRowLimit and Non-Optimized Queries

Queries that are not continuable pull just one set of a FullTextRowLimit number of rows from the full-text search engine. The value of FullTextRowLimit must be chosen carefully in this case.

Queries that are not optimized may return a subset of the number of rows matching the query if more than the FullTextRowLimit number of matches exists in the full-text index. When this condition occurs, the server throws a CBR_FULLTEXTROWLIMIT_EXCEEDED exception to notify the client of the condition, and that not all matches have been returned. This exception will be thrown when the caller iterates through the rows returned for the query, after the last row found.

If a value for FullTextRowLimit is not supplied, the value used will be the value of the ObjectStore.FullTextRowDefault property stored in the Global Configuration Database (GCD).

If a value for FullTextRowLimit is supplied and this value is greater than the value of the ObjectStore.FullTextRowMax property, the ObjectStore.FullTextRowMax value is used instead. The FullTextRowMax property enables system administrators to prevent queries from using too much processing time.

General Query Examples

This section provides some examples of simple SQL statements to retrieve various pieces of information.

Retrieve documents 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 containing 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-8D81B1DE8577E}

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'

The query above represents the SQL query behind the ObjectStore.getCheckoutList method. 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". Note that when retrieving a list of folders, the Workplace application issues the query in lieu of calling the Folder.getContainees method. Folder.getContainees returns all items in the folder, with no limit on the number of items returned. Using such queries, you can set the criteria to limit the number of items returned to only those required by your application.

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