Storage Hierarchy Data
Last updated on 13-Mar-2018 by Thomas P. Boesen
On this page
Example Setup
- Buildings: A and B
- Floors: S, 1, 2
- Rooms: 01, 02, 03, prefixed with a dot (.) in the hierarchy string
- Racks: A, B, C, prefixed with a dash(-) in the hierarchy string
- Shelves: 1, 2, 3, 4
- Boxes: Box467, Box987, prefixed with a dash(-) in the hierarchy string
StorageLocation Records
Type | StorageHierarchy | HierarchyTag | Name --- | --- | --- Building | A | A | A Building | B | B | B Floor | AS | S | S Floor | B1 | 1 | 1 Room | A2.01 | .01 | 01 Rack | B1.03-A | -A | A Shelf | B1.03-A2 | 2 | 2 Box | AS.02-B4-Box467 | -Box467 | Box467
Hierarchical Queries
For a location with ID 'STL0008', all parent locations can be found with this query:
SELECT hie.*
FROM PnS_StorageLocation AS cur
JOIN PnS_StorageLocation AS hie ON cur.StorageHierarchy LIKE hie.StorageHierarchy + '%'
WHERE cur.ID = 'STL0008'
ORDER BY StorageHierarchy
For a location with ID 'STL0017', all child locations can be found with this query:
SELECT hie.*
FROM PnS_StorageLocation AS cur
JOIN PnS_StorageLocation AS hie ON hie.StorageHierarchy LIKE cur.StorageHierarchy + '%'
WHERE cur.ID = 'STL0017'
ORDER BY hie.StorageHierarchy