Page Comparison

...

Collection Objects extract

...

SELECT
 CAST(d.objectID AS VARCHAR) objectID,
 d.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'
;

notes

Bibliographic References extract

field mapping

sql query

view: cs_bibliographicreferences

...

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.bibliography referenceNote
FROM
 Objects o
WHERE
 o.bibliography is not null
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.PubReferences referenceNote
FROM
 Objects o
WHERE
 o.PubReferences is not null
;

Annotations extract

field mapping

sql query

view: cs_annotations

...

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS Edition field' annotationType
FROM
 Objects o
WHERE
 o.edition IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.curatorialRemarks annotationNote,
 'TMS Curatorial remarks' annotationType
FROM
 Objects o
WHERE
 o.curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component Prep comments' annotationType
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 u.fieldValue annotationNote,
 t.userFieldType annotationType
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
;

notes

...