000029000 - Archer - SQL query - How to find records in a module that are not linked to any other record or are not referred by records in other applications

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029000
Applies ToRSA Product Set: Archer

RSA Version/Condition: 5.x
IssueHow to find records in a module that are not linked to any other record or are not referred by records in other applications
Resolution
/**************************************
Get all the tracking IDs from the application and store them in the #temp1
**************************************/
create table #temp1 (contentid int)
Insert into #temp1  select IVC.content_id from
tblIVContent IVC join tblLevel L on IVC.level_id = L.level_id
join tblModule M on M.module_id = L.module_id where M.module_id = '<Insert Module ID here>'
/**************************************
Find if any of the tracking IDs have a cross reference to other records or if other records have a cross reference to the applications records
**************************************/
select * from tblIVModuleContent where content_id IN (Select * from #temp1)OR record_id IN (Select * from #temp1)
/**************************************
Get the content ID's of the contact records that do not have a cross ref or are not referred as a related ref from other app
**************************************/
select contentid as RecordsNotLinke from #temp1 where contentid
NOT IN (select content_id from tblIVModuleContent) and contentid NOT IN (select record_id from tblIVModuleContent)
/**************************************
Drop the temp table
**************************************/
drop table #temp1

Attachments

    Outcomes