IBM Enterprise Records, Version 5.1.2    

Example report queries

You are given example SQL queries that are applicable to IBM® Enterprise Records reporting. They are designed to provide a starting point for you to base your queries on. These examples are based on the provided IBM Enterprise Records Crystal Report templates.

Audit Related

This query returns audited events that occur on record category instances that are located within the specified file plan node and within the specified time frame:

SELECT rc.PathName, ra.DateLastModified, ra.LastModifier, ra.AuditActionType
FROM   RMAudit ra 
INNER JOIN RecordCategory rc ON ra.SourceObjectId = rc.Id 
WHERE ra.DateLastModified >= 20090101T000000Z AND ra.DateLastModified <= 
20090131T000000Z
				AND rc.This INSUBFOLDER '/Records Management/File Plan'

The following query returns audited actions by a specific user on records that are located within a specific file plan location during a specific time frame:

SELECT ra.DateLastModified, ra.LastModifier, ra.AuditActionType,
ri.DocumentTitle, rmf.PathName 
FROM ((RMAudit ra INNER JOIN RecordInfo ri ON ra.SourceObjectId = ri.Id)
	INNER JOIN ReferentialContainmentRelationship RCR on ri.This = rcr.Head)
	INNER JOIN RMFolder rmf ON rcr.Tail = rmf.This 
WHERE ra.DateLastModified >= 2009-01-01 AND ra.DateLastModified <= 2009-01-12
      								AND ra.LastModifier = 'rangelj'       
										AND ri.This INFOLDER '/Records Management/File Plan/Cat1'

Container related

The following query returns record folders that are located under the specified file plan node and created by the specified user within the specified time frame:

SELECT rf.PathName, rf.DateCreated, l.LocationName As HomeLocation
FROM   RecordFolder rf
LEFT OUTER JOIN Location l ON rf.HomeLocation = l.This
WHERE  rf.DateCreated >= 2009-01-01 AND rf.DateCreated <= 2009-01-31
                AND rf.Creator = 'rangelj'
                AND rf.This INSUBFOLDER '/Records Management/File Plan'

Note the use of the left outer join to include the name of any HomeLocation instance that might be assigned to a particular record folder instance.

SELECT v.DateClosed, v.ReOpenedDate, v.VolumeName
FROM   Volume v
WHERE  v.DateClosed IS NOT NULL AND v.ReOpenedDate IS NOT NULL
       AND v.Parent = OBJECT('{24D91623-6C0A-4DB7-9C87-5C172F4A09D2}')

Note the use of the more efficient Volume.Parent property comparison in the WHERE clause instead of the alternative INFOLDER operator.

Disposition related

This query returns information that compares disposition schedule data with records that use an associated RecordType.

SELECT ds.DisposalScheduleName, a1.ActionType, rc.PathName, 
rcr.ContainmentName, rt.RecordTypeName
FROM (((( RecordInfo ri INNER JOIN RecordType rt ON 
ri.AssociatedRecordType = rt.This  )
   INNER JOIN DisposalSchedule ds ON rt.DisposalSchedule = ds.This )
   LEFT OUTER JOIN Action1 a1 ON ri.CurrentPhaseAction = a1.This )
   INNER JOIN ReferentialContainmentRelationship rcr ON ri.This = rcr.Head )
   INNER JOIN RMFolder rc ON rcr.Tail = rc.This
WHERE ri.IsDeleted = FALSE

When a query includes more than one join, parenthesis must be used to provide separation.

Here is a variation that returns containers that are associated with a specified disposition schedule.

SELECT rc.PathName, rc.Aggregation, rc.DateCreated, 
ds.DisposalScheduleName, A1.ActionType 
FROM (RMFolder rc INNER JOIN DisposalSchedule ds ON rc.DisposalSchedule = ds.This)
LEFT OUTER JOIN Action1 a1 ON rc.CurrentPhaseAction = a1.This 
WHERE rc.IsDeleted = FALSE AND ds.DisposalScheduleName = 'Sch_PreDefDate' 

This query reports vital records that are due for disposition with in a specified time period:

SELECT ri.VitalRecordNextReviewDate, rc.PathName, rcr.ContainmentName, 
       ri.DateCreated, ri.Creator, ri.VitalRecordReviewDate 
FROM (RecordInfo ri INNER JOIN ReferentialContainmentRelationship rcr 
ON ri.This = rcr.Head) 
INNER JOIN RMFolder rc ON rcr.tail = rc.This 
WHERE ri.IsDeleted = FALSE AND ri.IsVitalRecord = TRUE  
                           AND ri.VitalRecordNextReviewDate >= 2008-01-01 
                           AND ri.VitalRecordNextReviewDate <= 2009-12-31  

Hold Related

This query finds records that are associated with a specific hold during a specific time period. These records are also contained within a specified portion of the file plan.

SELECT ri.DocumentTitle, ri.DateCreated, rh.HoldName, rhl.DateCreated As 
HoldEstablishedOn 
FROM (RecordHold rh INNER JOIN RecordHoldLink rhl ON rh.This = rhl.Tail) 
      INNER JOIN RecordInfo ri ON rhl.Head = ri.This 
WHERE ri.IsDeleted = FALSE AND rh.HoldName = 'Smith vs. Jones' 
      AND rhl.DateCreated >= 2008-10-01 AND rhl.DateCreated <= 2008-12-31 
      AND ri.This INSUBFOLDER ('/Records Management/File Plan/ABC') 


Feedback

Last updated: November 2013
frmcg005.htm

© Copyright IBM Corporation 2013