Archiving Statements

To archive DMS objects yuuvis® RAD, you have to execute the archiving operation (via yuuvis® RAD management-studio) which takes an eSQL statement as input to select the objects to archive. This page gives you examples of the most commonly used statements.

Selecting Objects by Date/Time Criteria

Important

The sysitemcreationdate field selects the creation date of the entire object (item), whereas syscreationdate selects the creation date of the active version.
The datetime identifier takes input of the form yyyy-MM-dd hh:mm:ss. The only required part is yyyy but all left out parts will be autocompleted with 00.
Object type names and element (field) names are accessed by their technical names.

Statement	Description
`select * from dms:sysobject where sysitemcreationdate <= datetime'2018-01-23 13:14:15'`	This will archive all objects (of any document type) that were created before or exactly on January 23, 2018, 13:14:15.
`select * from dms:sysobject where syscreationdate < datetime'2018-07'`	This will archive all objects (of any document type) that were created and last modified before July 1, 2018, 00:00:00.
`select * from dms:document d where d.customfield1 = 'acknowledged' and d.customfield2 < datetime'2018-02-01'`	This will archive all objects of the type `document` where `customfield1` has the value `acknowledged` and `customfield2` contains a date that is January 31, 2018, 23:59:59.999 or earlier.
`select * from dms:sysobject where sysitemcreationdate <= dateadd(day, -7, currentdatetime())`	This will archive all objects (of any document type) that were created a week ago from now or earlier. The `dateadd` function can add or subtract a specified amount of time from the provided date: `dateadd(<unit>, <amount>, <date>)` The unit can be "second", "minute", "hour", "day", "week", "month", "year" The date can be taken from the index data, specified manually or `currentdate()` (start of current day)/`currentdatetime()` (now))

Selecting Objects by Creator/Modifier Criteria

Statement	Description
`select d from dms:sysobject d join ecm:syscreationtraceowner2user c2u join org:sysuser u` `where u.sysname = 'user4711'`	This will archive all objects (of any document type) of which `user4711` was the creator of the currently active version. This means either the object is in version 1 and `user4711` was the creator, or the version is in version 2 or higher and `user4711` was the last modifier.
`select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u` `where u.sysname = 'user4711'`	This will archive all objects (of any document type) of which `user4711` was the creator or the modifier of any version.
`select * from dms:sysobject (all versions) d join ecm:syscreationtraceowner2user c2u join org:sysuser u` `where d.sysversionnumber = 1 AND u.sysname = 'user4711'`	This will archive all objects (of any document type) of which `user4711` was the the creator of the first version. This means `user4711` created the (first version of the) item.

Selecting Objects by Index Data/ID

Statement	Description
`select * from dms:document where sysitemid = id'ED01B5E5996648088D0A474C37962C93'`	This will archive the DMS object of type `document` with the the item ID `ED01B5E5996648088D0A474C37962C93`.
`select * from dms:document where longnumber = 123456789L`	This will archive all DMS objects of type `document` where the field `longnumber` has the value `123456789`.
`select * from dms:document where string1 = 'ipsum'`	This will archive all DMS objects of type `document` where the field `string1` has the value `ipsum`.
`select * from dms:document where catalogfield = 'english'`	This will archive all DMS objects of type `document` where the field `catalogfield` has a catalog entry with data value `english`.
`select * from dms:document where orderstable containsrowwithcondition(orderstablerecord.order = 'order4711')`	This will archive all DMS objects of type `document` where the table `orderstable` has a row where the column `order` has the value `order4711`. This also applies to multi-tagging fields which are technically a table with one column. In this case, any element has to have the value `order4711`.