IBM FileNet P8, Version 5.2.1

Relational query syntax introduction

The SQL statement for a query begins with SELECT This, followed by the SQL commands for column definitions and search criteria. If the SQL statement includes a JOIN clause, the reference to This must be fully qualified with a table name, such as CustomObject.This.

Here are some examples of different types of syntax:

A simple query	`SELECT This, ContentSize, Creator, Id FROM Document WHERE (ContentSize > 0 OR Creator = 'Administrator')`
A table join (to find unfiled custom objects)	`SELECT CustomObject.This, CustomObject.Name FROM CustomObject LEFT JOIN ReferentialContainmentRelationship rcr ON Cust`
Date comparisons	`SELECT This, Creator, DateCreated, CurrentState FROM Document WHERE (DateCreated > 20010401 T000000Z AND Creator = 'Administrator')`
Custom search functions	In the following example, the custom search function is `CmRpt::FormatDate`. For information about search functions, see Creating custom search functions. `SELECT This, CmRpt::FormatDate(DateCreated, 'yyyy') FROM Document` In the following example, the custom search function is `NewRpt::UserRecord`. This function implements a Java™ code or Java script handler to populate columns in a dimension table. It is an example of finer aggregation because it groups by three different entities. The disadvantage of such a search is that it takes longer to run and populates a lot more rows. But it has the advantage of allowing for greater drill-down as well as slicing and dicing capabilities in OLAP reporting tools such as IBM® Cognos® Business Intelligence. `SELECT NewRpt::UserRecord(Creator) AS CmRptUserRecord, NewRpt::ClassRecord(ClassDescription) AS NewRptClassRecord, NewRpt::DateRecord(DateCreated) AS NewRptDateRecord, COUNT(Id) AS CmRptObjectCount, SUM(ContentSize) AS CmRptContentSize FROM Document GROUP BY CmRptUserRecord, NewRptClassRecord, NewRptDateRecord`