SQL search examples for generating a list of assets

Example 1: Locate unfiled documents

This query returns a list of unfiled documents

SELECT doc.[This], doc.[Id], doc.[ClassDescription], doc.[Name], 
  doc.[Creator], doc.[DateCreated], doc.[DateLastModified], doc.[LastModifier] 
FROM [Document] doc 
  LEFT JOIN [ReferentialContainmentRelationship] rcr 
  ON doc.[VersionSeries] = rcr.[VersionSeries] 
WHERE rcr.[Head] IS NULL AND (doc.[IsCurrentVersion] = TRUE 
  OR doc.[VersionStatus] = 3)

When you search for documents, you can use ‘IsCurrentVersion Equal To True OR VersionStatus Equal To Reservation (3)’ to reduce the size of the manifest by having the query results return only the current version and reservations. Because you can use FileNet® Deployment Manager export include options to have all document versions that are extracted during the export operation, the export manifest does not need to list them all. Additionally, selecting the Include all document versions option ensures that all versions of a document are in the same deployment data set. When you use the Include all document versions option, the import can create all of the versions in the proper order in the destination object store.

Example 2: Divide large set of objects into distinct result sets

This method uses ranges to create smaller results sets that can be added to distinct export manifests. If you have many objects, you can improve performance if you break the objects into smaller sets that are based on some fixed criteria, such as date ranges. The sets can then be processed separately, which provides better control over the entire process. For example, this method allows for faster identification of which set of objects need to be reprocessed if an error occurs. You can also set up multiple FileNet Deployment Manager workstations. The sets can then be processed concurrently.

The following example extends the previous query for unfiled documents to constrain the results to particular date ranges by using the DateCreated property:

SELECT doc.[This], doc.[Id], doc.[ClassDescription], doc.[Name], 
  doc.[Creator], doc.[DateCreated], doc.[DateLastModified], 
  doc.[LastModifier] 
FROM [Document] doc 
  LEFT JOIN [ReferentialContainmentRelationship] rcr 
  ON doc.[VersionSeries] = rcr.[VersionSeries] 
WHERE rcr.[Head] IS NULL AND (doc.[IsCurrentVersion] = TRUE 
  OR doc.[VersionStatus] = 3) 
  AND doc.[DateCreated] >= 20120110T000000Z 
  AND doc.[DateCreated] < 20120410T000000Z

Example 3: Documents contained by a specific folder, broken into multiple result sets by document creator

The Content Platform Engine SQL syntax extends the standard relational query language with operators that are tailored for an enterprise management system. For example, use the INFOLDER operation to fetch documents within a specific folder. The specific folder example also selects only the current versions of documents and splits the results by selecting documents that were created by a particular user. (In the example, the folder path is '\Folder\Subfolder' and the short name for the user who created the document is TUser.):

SELECT [This], [Id], [ClassDescription], [Name], [Creator], 
  [DateCreated], [DateLastModified], [LastModifier] 
FROM [Document] 
WHERE ([This] INFOLDER '\Folder\Subfolder') AND ([IsCurrentVersion] = TRUE) 
  AND ([Creator] = 'TUser')