os_container_hierarchy_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

container_id

BIGINT

null

hierarchy

TEXT

65535

√

null

View Definition

select `h`.`DESCENDENT_ID` AS `container_id`,group_concat(concat((case when (`p`.`POS_ONE_STR` is not null) then concat(' (',(case when (`pr`.`POS_LABELING_MODE` = 'TWO_D') then concat(`p`.`POS_TWO_STR`,' x ',`p`.`POS_ONE_STR`) when (`pr`.`POS_LABELING_MODE` = 'LINEAR') then (((`p`.`POS_TWO` - 1) * (case when (`pr`.`POS_ASSIGNMENT` like 'HZ%') then `pr`.`NO_OF_COLS` else `pr`.`NO_OF_ROWS` end)) + `p`.`POS_ONE`) else 'NA' end),') -> ') when (`p`.`IDENTIFIER` is not null) then ' -> ' else '' end),`s`.`NAME`) order by `t`.`hl` DESC separator '') AS `hierarchy` from ((((`openspecimen_demo`.`os_containers_hierarchy` `h` join `openspecimen_demo`.`os_storage_containers` `s` on((`s`.`IDENTIFIER` = `h`.`ANCESTOR_ID`))) join (select `openspecimen_demo`.`os_containers_hierarchy`.`ANCESTOR_ID` AS `ancestor_id`,count(0) AS `hl` from `openspecimen_demo`.`os_containers_hierarchy` group by `openspecimen_demo`.`os_containers_hierarchy`.`ANCESTOR_ID`) `t` on((`t`.`ancestor_id` = `s`.`IDENTIFIER`))) left join `openspecimen_demo`.`os_container_positions` `p` on((`p`.`OCCUPYING_CONTAINER_ID` = `s`.`IDENTIFIER`))) left join `openspecimen_demo`.`os_storage_containers` `pr` on((`pr`.`IDENTIFIER` = `p`.`STORAGE_CONTAINER_ID`))) where (`s`.`ACTIVITY_STATUS` <> 'Disabled') group by `h`.`DESCENDENT_ID`

Columns

View Definition

Possibly Referenced Tables/Views