...
view: cs_fieldcollectionnotes (refreshed 2012-0305-1314) (NEEDS UPDATE)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection date: 'fieldCollectionNote_prefix,
d.remarks fieldCollectionNote
FROM
ObjDates d
JOIN Objects o on d.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
AND d.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection place: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
FROM
SiteObjXrefs x
JOIN Objects o ON x.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND x.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collection place: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
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 ('archaeological site', 'card header', 'provenience')
AND x.remarks IS NOT NULL AND x.remarks != ''
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
'Field collector: 'fieldCollectionNote_prefix,
x.remarks fieldCollectionNote
FROM
ConXrefs x
JOIN roles r ON x.roleID = r.roleID
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 lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
AND x.remarks IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
(CASE WHEN t.textTypeID = 8 THEN 'NAGPRA site concordance note: ' WHEN t.textTypeID IN (14, 58, 102) THEN tt.textType + ': ' ELSE '' END) fieldCollectionNote_prefix,
t.textEntry fieldCollectionNote
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 (8, 14, 58, 90, 102)
;
|
notes
- NOTE: 'fieldCollectionNote_prefix' should be prepended to 'fieldCollectionNote' prior to import (this can't be done here because some fields are type TEXT which cannot be concatenated in SQL)
- NOTE: multiple notes per object should be concatenated into single (non-repeating) field fieldCollectionNote, separated by line breaks