Object Description data mapping

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID contentPerson
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
 JOIN constituents c ON x.constituentID = c.constituentID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.constituentTypeID = 1
 AND lower(r.ROLE) IN ('subject')
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID contentPlace
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('subject')
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentPeople
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (110, 111)
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry contentNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 85
;

SELECT     	CAST(o.objectID AS VARCHAR) objectID, 
		NULL inscriptionContent_prefix, 
		o.Signed inscriptionContent,
		NULL inscriptionType,
		NULL inscriptionTransliteration,
		NULL inscriptionTranslation,
		NULL inscriptionInterpretation,
		NULL inscriptionLanguage,
		NULL inscriptionScript,
		NULL inscriptionPosition,
		NULL inscriptionMethod,
		NULL inscriptionDate
FROM         Objects o
WHERE     o.IsVirtual = 0 AND o.IsTemplate = 0 AND o.Signed IS NOT NULL
UNION ALL
SELECT     	CAST(o.objectID AS VARCHAR) objectID, 
		(CASE WHEN t.textTypeID IN (19, 20, 22, 123) THEN substring(tt.TextType, 7, 1) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' ELSE '' END) inscriptionContent_prefix, 
		(CASE WHEN len(t.MTB_Inscription) > 2 THEN t.MTB_Inscription ELSE t.TextEntry END) inscriptionContent,
		(CASE WHEN t.textTypeID IN (19, 20, 22, 123) THEN 'coin inscription' ELSE '' END) inscriptionType,
		t.MTB_Transliteration inscriptionTransliteration,
		t.MTB_Translation inscriptionTranslation,
		t.MTB_Interpretation inscriptionInterpretation,
		t.MTB_Language inscriptionLanguage,
		t.MTB_Script inscriptionScript,
		t.MTB_InscriptionPosition inscriptionPosition,
		t.MTB_InscriptionMethod inscriptionMethod,
		t.MTB_Dated inscriptionDate
FROM         TextEntries t JOIN
                     Objects o ON t .ID = o.objectID JOIN
                     TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE     t.tableID = 108 AND o.IsVirtual = 0 AND o.IsTemplate = 0 AND t.TextTypeID IN (19, 20, 22, 78, 87, 123)