Page Comparison

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.ObjectNumber objectNumber,
 'no' isComponent,
 o.CatRais pahmaCollection,
 d.Department pahmaTmsLegacyDepartment,
 o.HistAttributions objectHistoryNote,
 (CASE WHEN lower(o.LoanClass) not in ('', 'catalog card', 'inventory', 'ledger') THEN o.LoanClass END) pahmaTms2003DataSource,
 o.Markings distinguishingFeatures,
 o.Medium material,
 o.Notes comments,
 s.ObjectStatus pahmaObjectStatus,
 t.ObjectType collection,
 i.CSpaceID nagpraInventoryName,
 o.MTB_Portfolio portfolioSeries,
 (CASE WHEN o.MTB_ResponsibleDept = 1 THEN 'Natasha Johnson'
       WHEN o.MTB_ResponsibleDept = 2 THEN 'Leslie Freund'
       WHEN o.MTB_ResponsibleDept = 3 THEN 'Victoria Bradshaw'
       WHEN o.MTB_ResponsibleDept = 4 THEN 'Alicja Egbert'
       WHEN o.MTB_ResponsibleDept = 5 THEN 'No collection manager (Registration)'
       WHEN o.MTB_ResponsibleDept = 8 THEN 'uncertain' END) responsibleDepartment,
 o.Signed inscriptionContent,
 b.optionID pahmaNagpraCodeLegacy,
 c.integer3 inventoryCount
FROM
 Objects o
 LEFT OUTER JOIN Departments d ON o.departmentID = d.departmentID
 LEFT OUTER JOIN ObjectStatuses s ON o.objectStatusID = s.objectStatusID
 LEFT OUTER JOIN ObjectTypes t ON o.objectTypeID = t.objectTypeID
 LEFT OUTER JOIN MTB_NagpraInvNames i ON o.paperFileRef = i.LongTMSname
 LEFT OUTER JOIN MTB_NagpraBurialCodes b ON o.state = b.code
 LEFT OUTER JOIN ObjContext c ON o.objectID = c.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentNumber objectNumber,
 'yes' isComponent,
 NULL pahmaCollection,
 NULL pahmaTmsLegacyDepartment,
 NULL objectHistoryNote,
 NULL pahmaTms2003DataSource,
 NULL distinguishingFeatures,
 NULL material,
 NULL commentspahmaObjectStatus,
 NULL pahmaObjectStatuscollection,
 NULL collection,
 NULL nagpraInventoryName,
 NULL portfolioSeries,
 NULL responsibleDepartment,
 NULL inscriptionContent,
 NULL pahmaNagpraCodeLegacy,
 NULL inventoryCount
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.description briefDescription
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.description IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.physDesc IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.physDesc briefDescription
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.physDesc IS NOT NULL
UNION ALL 
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry briefDescription
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 (6, 7, 23)
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 xo.constituentIDNotes contentPersoncomment
FROM
 ConXrefsObjects o
xWHERE
 JOINo.IsVirtual roles= r0 ONAND xo.roleIDIsTemplate = r.roleID0
 AND o.Notes IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry comment
FROM
 TextEntries t
 JOIN objectsObjects o ON xt.ID = o.objectID
 JOIN constituentsTextTypes ctt ON xt.constituentIDTextTypeID = ctt.constituentIDTextTypeID
WHERE
 xt.tableID = 108
 AND xo.activeIsVirtual = 1
 AND o.IsVirtual = 0 0 AND o.IsTemplate = 0
 AND ct.constituentTypeIDTextTypeID = 1
 AND lower(r.ROLE) IN ('subject')
27
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID contentPlace
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID(CASE WHEN t.textTypeID = 37 THEN tt.TextType + ': ' ELSE '' END) materialComponentNote_prefix,
 t.TextEntry materialComponentNote
FROM
 TextEntries t
 JOIN Objects o ON xt.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 xt.tableID = 108
 AND x.active = 1
 AND o.o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID
= 346
 AND lower(t.thesXrefType)TextTypeID IN ('subject'37, 92)
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry assocPeopleNote,
 (case when t.textTypeID = 49 then 'nagpraCulturalAffiliation' end) assocPeopleTypesubstring(tt.TextType, 1, 7) + lower(substring(tt.TextType, 8, len(tt.TextType))) + ': ' nagpraCulturalDetermination_prefix,
 t.TextEntry nagpraCulturalDetermination
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = 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 (32, 49, 97, 99)
; tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID = 121
 ;