Manitou Core - SQL Query - SELECT BLANK NAME CONTACTS

01/03/2024 10:59 am EST

/****** Script SELECT BLANK NAME CONTACTS ******/

/*
Run script below prior to removing any data
Highlight from Select...contlist
*/

-- Select * into tablebackups..contlist_OrphRecCleanUp from contlist

/*
This script lists all account(s) that have blank contact(s)
To remove: highlight from DELETE......NULL
*/

SELECT *
--DELETE L
FROM CONTLIST L
LEFT JOIN CONTACT T ON L.REFSERIALNO = T.SERIALNO AND L.REFCONTTYPE = T.CONTTYPE
LEFT JOIN CONTACT OWN ON L.SERIALNO = OWN.SERIALNO AND L.CONTTYPE = OWN.CONTTYPE
WHERE T.SERIALNO IS NULL AND OWN.SERIALNO IS NOT NULL

/* This scipt lits only the Customer ID(s) of where the blank contact(s) are located */

SELECT CONTID FROM CUSTOMER WHERE SERIALNO IN
(SELECT SERIALNO FROM CONTLIST WHERE NOT EXISTS (SELECT * FROM CONTACT WHERE CONTACT.CONTTYPE = CONTLIST.REFCONTTYPE AND
CONTACT.SERIALNO = CONTLIST.REFSERIALNO))

Was this article helpful?