How to retrieve all versions of a document using SQL queries in Filenet P8?

Question

I'm looking for a way to retry and if needed remove all documents located inside the tree of a given folder root with all of their versions, not just the current one.

The easiest way might be a SQL query with a batch deletion. Since previous version of a document are not filed in a Folder, I proceeded to do it this way:

• Perform a SELECT request on Document class
• Add a LEFT JOIN between a document version (Document AS doc) and other versions (Document AS doc2) using version_series key.
• Filter to get only current version from other versions (doc2.IsCurrentVersion = true)
• Filder to get only documents whose current version is filed in a given Folder tree (INSUBFOLDER clause)

Additional step:

• Left joins with ReferentialContainmentRelationShip and Folder classes to get the path name of the folder of the document current version

The final request looks like below:

SELECT doc.this,
       doc.majorversionnumber AS "MAJV",
       doc.minorversionnumber AS "MINV",
       doc.IsCurrentVersion AS "Is Current",
       doc2.This AS "Current version",
       f.pathname AS "Current version folder"
FROM ((Document doc
       LEFT JOIN Document doc2 ON
                 doc2.VersionSeries = doc.VersionSeries)
       LEFT JOIN ReferentialContainmentRelationShip rcr ON
                 doc2.VersionSeries = rcr.VersionSeries)
LEFT JOIN Folder f ON f.This = rcr.tail
WHERE doc2.IsCurrentVersion = true AND
      doc2.this INSUBFOLDER '/path/to/a/folder'
ORDER BY doc2.This

Is this a good and not overkill way to achieve what the goal?

Answer 1

Deleting a document will remove all versions. You would have to do a Demote to just remove the Current Version.