IBM Enterprise Records V5.1.2    

报告查询示例

系统提供了适用于 IBM® Enterprise Records 报告的 SQL 查询示例。这些示例旨在提供查询基于的起始点。这些示例基于提供的 IBM Enterprise Records Crystal Report 模板。

相关审计

此查询会返回位于所指定归档方案节点中并处于所指定时间范围内的记录类别实例上发生的审计事件。

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'

下面的查询会返回在特定时间范围内特定用户针对位于特定归档方案位置中的记录执行的审计操作。

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'

相关容器

下面的查询会返回位于所指定归档方案节点下且由所指定用户在所指定时间范围内创建的记录文件夹:

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'

注意,使用 left outer join 可包含可能指定给特定记录文件夹实例的任意 HomeLocation 实例的名称。

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}')

注意,在 WHERE 子句中使用更有效的 Volume.Parent 属性比较,而不是使用备用的 INFOLDER 运算符。

相关处理

此查询会返回将处置计划日期与使用相关联 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

当查询包括多个连接时,必须使用圆括号进行分隔。

以下变体将返回与指定处理调度关联的容器。

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' 

以下查询报告指定时间段内应进行处理的重要记录:

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  

相关挂起

此查询查找特定时间段内与特定挂起相关联的记录。这些记录也包含在归档计划的指定部分中。

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') 


反馈

最近一次更新时间: 2013 年 11 月
frmcg005.htm

© Copyright IBM Corporation 2013