...
view: cs_associatedpeoples (refreshed 2012-0304-1220) (NEEDS UPDATE)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT CAST(c.objectID AS VARCHAR) objectID, c.culture assocPeople, NULL assocPeopleType, NULL assocPeopleNote FROM ObjContext c JOIN Objects o ON c.objectID = o.objectID WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND c.culture IS NOT NULL UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, NULL assocPeople, (case when t.textTypeID = 49 then 'nagpraCulturalAffiliation' end) assocPeopleType, t.TextEntry assocPeopleNote 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 (32, 49, 97, 99) ; |
...
view: cs_associateddates (refreshed 2012-03-12) (NEEDS UPDATE04-20)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(d.objectID AS VARCHAR) objectID,
(CASE WHEN d.eventType != '[not entered]' THEN lower(d.eventType) END) assocDateType,
d.dateText assocDate_dateDisplayDate,
NULL assocDate_dateNote,
(CASE WHEN d.dateBegSearch != '0' THEN d.dateBegSearch END) assocDate_dateEarliestSingleYear,
(CASE WHEN d.dateEndSearch != '0' THEN d.dateEndSearch END) assocDate_dateLatestYear,
d.dayBegSearch assocDate_dateEarliestSingleDay,
d.dayEndSearch assocDate_dateLatestDay,
d.monthBegSearch assocDate_dateEarliestSingleMonth,
d.monthEndSearch assocDate_dateLatestMonth,
d.remarks assocDateNote
FROM
ObjDates d
JOIN Objects o ON d.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND lower(d.eventType) NOT IN ('accessioned', 'date collected', 'secondary collection date', 'tertiary collection date', 'date made')
AND ((d.dateText IS NOT NULL AND d.dateText != '') OR d.dateBegSearch != 0)
UNION ALL
SELECT
CAST(c.objectID AS VARCHAR) objectID,
NULL assocDateType,
c.period assocDate_dateDisplayDate,
NULL assocDate_dateNote,
NULL assocDate_dateEarliestSingleYear,
NULL assocDate_dateLatestYear,
NULL assocDate_dateEarliestSingleDay,
NULL assocDate_dateLatestDay,
NULL assocDate_dateEarliestSingleMonth,
NULL assocDate_dateLatestMonth,
NULL assocDateNote
FROM
ObjContext c
JOIN Objects o ON c.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND c.period IS NOT NULL
UNION ALL
SELECT
CAST(c.objectID AS VARCHAR) objectID,
NULL assocDateType,
c.reign assocDate_dateDisplayDate,
'produced during reign of' assocDate_dateNote,
NULL assocDate_dateEarliestSingleYear,
NULL assocDate_dateLatestYear,
NULL assocDate_dateEarliestSingleDay,
NULL assocDate_dateLatestDay,
NULL assocDate_dateEarliestSingleMonth,
NULL assocDate_dateLatestMonth,
NULL assocDateNote
FROM
ObjContext c
JOIN Objects o ON c.objectID = o.objectID
WHERE
o.IsVirtual = 0 AND o.IsTemplate = 0
AND c.reign IS NOT NULL
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
NULL assocDateType,
NULL assocDate_dateDisplayDate,
NULL assocDate_dateNote,
NULL assocDate_dateEarliestSingleYear,
NULL assocDate_dateLatestYear,
NULL assocDate_dateEarliestSingleDay,
NULL assocDate_dateLatestDay,
NULL assocDate_dateEarliestSingleMonth,
NULL assocDate_dateLatestMonth,
t.TextEntry assocDateNote
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 = 100
;
|
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
TextEntries | textEntry | assocObject | where textTypeID is 104 or 106 |
sql query
view: (NEEDS UPDATE cs_associatedobjects (refreshed 2012-04-20)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, t.TextEntry assocObject 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 (104, 106) ; |
...
TMS table name | TMS field name | CSpace field name | notes |
|---|---|---|---|
TextEntries | textEntry | assocPlace | where textTypeID is 45 |
sql query
view: (NEEDS UPDATE cs_associatedplaces (refreshed 2012-04-20)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, t.TextEntry assocPlace, 'used' assocPlaceType 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 = 45 ; |
...
view: cs_previousowners (refreshed 2012-03-12) (NEEDS UPDATE04-20)
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
CAST(o.objectID AS VARCHAR) objectID,
x.constituentID previousOwner,
x.remarks ownershipNote
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 ('original owner', 'previous owner', 'previous donor (to other museum)')
UNION ALL
SELECT
CAST(o.objectID AS VARCHAR) objectID,
NULL previousOwner,
t.TextEntry ownershipNote
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 = 93
;
|
...