acraigbray
11/27/2017 - 5:25 PM

[Find Recursive Joins in ICRM Global Joins] per this article recursive joins can sometimes cause the Group Editor or the DB Manager to cras

[Find Recursive Joins in ICRM Global Joins] per this article recursive joins can sometimes cause the Group Editor or the DB Manager to crash. http://customerfx.com/article/solving-the-infor-crm-or-saleslogix-database-manger-crash #SQL #InforCRM

Find Recursive Joins in ICRM Global Joins
content_copyfile_download
SELECT 'Recursive joins' as IssueType ,'JoinId: ' + J1.JoinID +' FromTable.Field: ' + J1.FROMTABLE +'P'+ J1.FROMFIELD + ' TOTable.Field: '+ J1.TOTABLE +'.'+ J1.TOFIELD as 'Result'
FROM
SYSDBA.JOINDATA J1, SYSDBA.JOINDATA J2
WHERE
J1.FROMTABLE = J2.TOTABLE
AND J1.TOTABLE = J2.FROMTABLE
AND J1.FROMFIELD = J2.TOFIELD
AND J1.TOFIELD = J2.FROMFIELD
Union
--Checks for bad joins
select 'Bad join' as IssueType , 'JoinId: '+j.JoinID + ' From: '+j.FromTable+'.'+j.FromField + ' TO: '+ j.ToTable+'.'+j.ToField as Result
from sysdba.joindata j
left outer join sysobjects so1 on j.fromtable = so1.name
left outer join sysobjects so2 on j.totable = so2.name
where (so1.name is null or so2.name is null) and j.fromtable <>'*'