000029435 - Query to retrieve information about cross reference relationships

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 Number000029435
Applies ToRSA Product Set : Archer

RSA Version/Condition : 5.x

Platform : Windows
IssueQuery to retrieve information about cross reference relationships
ResolutionQuery for versions pre 5.5.2:
select FD .field_id, FD. field_name,FD .field_desc,
M.module_name ,M1. module_name as RefApp, FD1. field_name, FDC.default_grid_display , FDC.max_value ,
FDC.min_value , FDC .display_control_id, DC. display_control_name, ASO.aso_status_name , FDC.allow_lookup
from tblIVFieldDef FD
join tblLevel L ON L .level_id = FD .level_id
join tblModule M ON M .module_id = L .module_id
join tblIVFieldRelationship FR ON FR .reference_field_id = FD .field_id
join tblIVFieldDef FD1 ON FD1 .field_id = FR .related_field_id
join tblLevel L1 On L1 .level_id = FD1 .level_id
join tblASOStatus ASO ON ASO .aso_status = FD .aso_status
join tblModule M1 ON M1 .module_id = L1 .module_id
left outer join tblIVFieldRelationship FR1 ON FR1.related_field_id = FD1.field_id
left outer join tblIVFieldDefCrossRef FDC ON FDC.field_id = FD.field_id
left outer join tblIVDisplayControl DC ON DC.display_control_id = FDC.display_control_id
where FD .field_type_id = 9

Query for versions 5.5.2 or later:
 
select FD .field_id, FT.field_name,FT.field_desc,
MT.module_name ,MT1.module_name as RefApp, FT.field_name, FDC.default_grid_display , FDC.max_value ,
FDC.min_value , FDC .display_control_id, DC. display_control_name, ASO.aso_status_name , FDC.allow_lookup
from tblIVFieldDef FD
join tblLevel L ON L .level_id = FD .level_id
join tblModule M ON M .module_id = L .module_id
join tblModuleTranslation MT ON MT.module_id = M.module_id
join tblIVFieldRelationship FR ON FR .reference_field_id = FD .field_id
join tblIVFieldDef FD1 ON FD1 .field_id = FR .related_field_id
join tblFieldTranslation FT ON FT.field_id = FD.field_id
join tblLevel L1 On L1 .level_id = FD1 .level_id
join tblASOStatus ASO ON ASO .aso_status = FD .aso_status
join tblModule M1 ON M1 .module_id = L1 .module_id
join tblModuleTranslation MT1 ON MT1.module_id = M1.module_id
left outer join tblIVFieldRelationship FR1 ON FR1.related_field_id = FD1.field_id
left outer join tblIVFieldDefCrossRef FDC ON FDC.field_id = FD.field_id
left outer join tblIVDisplayControl DC ON DC.display_control_id = FDC.display_control_id
where FD .field_type_id = 9

 

Attachments

    Outcomes