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,
 MTB_PlaceTermID fieldCollectionPlace,
 Notes comments,
 s.ObjectStatus pahmaObjectStatus,
 t.ObjectType collection,
 i.CSpaceID nagpraInventoryName,
 o.MTB_Portfolio portfolioSeries,
 Provenance fieldLocVerbatim,
 (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,
 Signed inscriptionContent,
 b.optionID pahmaNagpraCodeLegacy,
 Title title,
 c.integer3 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 fieldCollectionPlace,
 NULL comments,
 NULL pahmaObjectStatus,
 NULL collection,
 NULL nagpraInventoryName,
 NULL portfolioSeries,
 NULL fieldLocVerbatim,
 NULL responsibleDepartment,
 NULL inscriptionContent,
 NULL pahmaNagpraCodeLegacy,
 NULL title,
 NULL inventoryCount
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
 CAST(c.objectID AS VARCHAR) parentObjectIDobjectID_1,
  'c' + CAST(c.componentID AS VARCHAR) childObjectIDobjectID_2,
  'Object-Component' relationshipType
FROM
  ObjComponents c
WHERE
  c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
;

SELECT
  CAST(n.ID AS VARCHAR) objectID,
  n.altnum pahmaAltNum,
  n.description pahmaAltNumType,
  n.remarks pahmaAltNumNote
FROM
  AltNums n
WHERE
  n.tableID = 108
 AND n.altnum IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentNumber pahmaAltNum,
 NULL pahmaAltNumType,
 NULL pahmaAltNumNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentNumber IS NOT NULL
 AND c.componentNumber != o.objectNumber
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
UNION ALLAND SELECTo.objectName IS 'c' NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 NULL objectNameNote
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.componentName IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.componentName objectName,
 NULL objectNameLanguage,
 'Component name' objectNameNote
FROM
 ObjComponents c
 JOIN Objects o ON c.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.componentName IS NOT NULL
 AND coalesce(o.objectName,'') != c.componentName
UNION ALL
SELECT
 CAST(n.objectID AS VARCHAR) objectID,
 n.objectName,
 (CASE WHEN n.languageID != '0' THEN 'urn:cspace:pahma.cspace.berkeley.edu:vocabularies:name(languages):item:name(' + rtrim(l.MTB_ISOcode) + END') objectNameLanguage,
''' + (CASE WHEN n.objectNameTypeID != '0' THEN charindex('/', l.language) > 0 THEN substring(l.language, 1, charindex('/', l.language) - 1) ELSE l.language END) + '''' END) objectNameLanguage,
 (CASE WHEN n.objectNameTypeID != '0' THEN t.objectNameType ELSE '' END) + (CASE WHEN n.objectNameTypeID != '0' AND n.remarks is not null THEN '; ' ELSE '' END) + coalesce(n.remarks, '') objectNameNote
FROM
 objectNames n
 LEFT OUTER JOIN objectNameTypes t ON n.objectNameTypeID = t.objectNameTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
WHERE
 n.objectName IS NOT NULL
;

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(uo.objectID AS VARCHAR) objectID,
 c.CSoptionID pahmaEthnographicFileCodeTitle title,
 NULL titleLanguage,
 NULL titleType
FROM
 ObjUserFieldsObjects o
uWHERE
 JOIN MTB_EthnographicUseCodes c ON u.FieldValue = c.FieldValue
WHERE
 u.FieldTypeID = 17
;

JOIN titleTypes t ON n.titleTypeID = t.titleTypeID
 LEFT OUTER JOIN languages l ON n.languageID = l.languageID
;

SELECT
 CAST(do.objectID AS VARCHAR) objectID,
 s.ObjectStatus pahmaObjectStatus
FROM
 Objects o
 LEFT OUTER JOIN ObjectStatuses s dON o.dateText objectProductionDate_dateDisplayDate,
 (CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) objectProductionDate_dateEarliestSingleYear,
 (CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) objectProductionDate_dateLatestYear,
 d.dayBegSearch objectProductionDate_dateEarliestSingleDay,
 d.dayEndSearch objectProductionDate_dateLatestDay,
 d.monthBegSearch objectProductionDate_dateEarliestSingleMonth,
 d.monthEndSearch objectProductionDate_dateLatestMonth,
 d.remarks objectProductionNote
FROM
 ObjDates d
WHERE
 lower(d.eventType) = 'date made'
;objectStatusID = s.objectStatusID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND o.objectStatusID IS NOT NULL
UNION ALL
SELECT
 CAST(sf.objectID AS VARCHAR) objectID,
 fl.FlagLabel pahmaObjectStatus
FROM
 StatusFlags sf
 LEFT OUTER JOIN FlagLabels fl ON sf.FlagID = fl.FlagID
WHERE
   fl.FlagID IS NOT NULL
ORDER BY objectID