...
sql query
view: cs_objects (NEEDS UPDATErefreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
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, 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, 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 pahmaObjectStatus, NULL collection, NULL nagpraInventoryName, NULL portfolioSeries, NULL responsibleDepartment, NULL pahmaNagpraCodeLegacy, NULL inventoryCount FROM ObjComponents c WHERE c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1) ; |
...
view: cs_objectobjectrelationships (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(c.objectID AS VARCHAR) objectID_1, 'c' + CAST(c.componentID AS VARCHAR) objectID_2, 'Object-Component' relationshipType FROM ObjComponents c WHERE c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1) UNION ALL SELECT CAST(oa.ID1 AS VARCHAR) objectID_1, CAST(oa.ID2 AS VARCHAR) objectID_2, r.relation1 + '-' + r.relation2 relationshipType FROM Associations oa, Objects obj1, Objects obj2, Relationships r WHERE oa.tableID = 108 AND oa.ID1 = obj1.objectID AND oa.ID2 = obj2.objectID AND obj1.IsVirtual = 0 AND obj1.IsTemplate = 0 AND obj2.IsVirtual = 0 AND obj2.IsTemplate = 0 AND oa.relationshipID = r.relationshipID ; |
...
sql query
view: cs_numberofobjects (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, o.objectCount numberOfObjects FROM Objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND o.objectID NOT IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1) UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, (CASE WHEN c.compCount > o.objectCount THEN c.compCount ELSE o.objectCount END) numberOfObjects FROM Objects o JOIN ObjComponents c ON o.objectID = c.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) UNION ALL SELECT 'c' + CAST(c.componentID AS VARCHAR) objectID, c.compCount numberOfObjects FROM ObjComponents c WHERE c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1) ; |
...
view: cs_alternatenumbers (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
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 ; |
...
view: cs_briefdescriptions (NEEDS UDPATErefreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
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)
;
|
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
Objects | Notes | comment |
|
TextEntries | textEntry | comment | where textTypeID is 27 |
sql query
view: (NEEDS UPDATE cs_objectcomments (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, o.Notes comment FROM Objects o WHERE o.IsVirtual = 0 AND o.IsTemplate = 0 AND o.Notes IS NOT NULL UNION ALL SELECT CAST(o.objectID AS VARCHAR) objectID, t.TextEntry comment 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 = 27 ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
TextEntries | textEntry | materialComponentNote | where textTypeID is 37 or 92 |
sql query
view: (NEEDS UPDATEcs_materialsnotes (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, (CASE WHEN t.textTypeID = 37 THEN tt.TextType + ': ' ELSE '' END) materialComponentNote_prefix, t.TextEntry materialComponentNote 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 (37, 92) ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
TextEntries | textEntry | nagpraCulturalDetermination | where textTypeID is 121 |
sql query
view: (NEEDS UPDATE cs_nagpraculturaldeterminations (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, substring(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 = tt.TextTypeID WHERE t.tableID = 108 AND o.IsVirtual = 0 AND o.IsTemplate = 0 AND t.TextTypeID = 121 ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
TextEntries | textEntry | repatriationNote | where textTypeID is 81 |
sql query
view: (NEEDS UPDATE cs_repatriationnotes (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, t.TextEntry repatriationNote 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 = 81 ; |
...
TMS table name | TMS field name | CSpace field name | notes |
---|---|---|---|
TextEntries | textEntry | notes | where textTypeID is 96 |
sql query
view: (NEEDS UPDATE cs_taxonomicdeterminationnotes (refreshed 2012-04-10)
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT CAST(o.objectID AS VARCHAR) objectID, t.TextEntry notes 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 = 96 ; |
...