-- NickAltNameConflict.sql
/*
2015-12-30 Tom Holden ve3meo

Returns Record Number and Nicknames of Persons who have 
both an Alternate Nickname and a Primary name with an embedded 
nickname or non-empty NickName field and the nickname values
in the corresponding fields do not match. Use RIN to find the 
person in RootsMagic Explorer to revise as needed.
*/

DROP VIEW IF EXISTS HasNickPrime
;
CREATE TEMP VIEW IF NOT EXISTS HasNickPrime AS
SELECT OwnerID AS RIN
     , TRIM(REPLACE(SUBSTR(Given,INSTR(Given,'"')),'"','')) AS NickGiven
     , TRIM(NickName) AS NickField
FROM NameTable 
WHERE 
(LENGTH(NickName)  -- Nickname field not empty
  OR INSTR(Given,'"')-- has embedded nickname in ""
)
AND IsPrimary      -- Primary Name
ORDER BY OwnerID
;

SELECT 
     HasNickPrime.*
     , NameID AS AltNameID
     , TRIM(REPLACE(SUBSTR(Given,INSTR(Given,'"')),'"','')) AS AltNickGiven
     , Nickname AS AltNickField
FROM NameTable
JOIN HasNickPrime ON OwnerID = RIN
WHERE NameType = 6 -- Alternate Nickname
AND 
(NickGiven NOT LIKE AltNickGiven
  OR 
 NickField NOT LIKE AltNickField
)
;