> ## Documentation Index > Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt > Use this file to discover all available pages before exploring further. # BOOK_HIERARCHY The BOOK\_HIERARCHY table contains the multi-level book organizational structure and the desk-level information. | Column Name | Type | Not Null | Cube Field | Description | | ------------------ | ------ | -------- | ---------- | ------------------------------------------------------------------------------------------------ | | AS\_OF\_DATE | DATE | Y | | Timestamp (at close of business) for the data. | | BOOK | STRING | Y | | Leaf node of the book hierarchy. This matches the last non-\_DATAMEMBER\_ node in levels 1 - 15. | | HIERARCHY\_LEVEL1 | STRING | | Level 1 | Node at level 1 of the book hierarchy. | | HIERARCHY\_LEVEL2 | STRING | | Level 2 | Node at level 2 of the book hierarchy. | | HIERARCHY\_LEVEL3 | STRING | | Level 3 | Node at level 3 of the book hierarchy. | | HIERARCHY\_LEVEL4 | STRING | | Level 4 | Node at level 4 of the book hierarchy. | | HIERARCHY\_LEVEL5 | STRING | | Level 5 | Node at level 5 of the book hierarchy. | | HIERARCHY\_LEVEL6 | STRING | | Level 7 | Node at level 6 of the book hierarchy. | | HIERARCHY\_LEVEL7 | STRING | | Level 6 | Node at level 7 of the book hierarchy. | | HIERARCHY\_LEVEL8 | STRING | | Level 8 | Node at level 8 of the book hierarchy. | | HIERARCHY\_LEVEL9 | STRING | | Level 9 | Node at level 9 of the book hierarchy. | | HIERARCHY\_LEVEL10 | STRING | | Level 10 | Node at level 10 of the book hierarchy. | | HIERARCHY\_LEVEL11 | STRING | | Level 11 | Node at level 11 of the book hierarchy. | | HIERARCHY\_LEVEL12 | STRING | | Level 12 | Node at level 12 of the book hierarchy. | | HIERARCHY\_LEVEL13 | STRING | | Level 13 | Node at level 13 of the book hierarchy. | | HIERARCHY\_LEVEL14 | STRING | | Level 14 | Node at level 14 of the book hierarchy. | | HIERARCHY\_LEVEL15 | STRING | | Level 15 | Node at level 15 of the book hierarchy. | ## Unique Key | Columns | | ------------ | | AS\_OF\_DATE | | BOOK | ## Incoming Joins | Source Table | Source Columns | Target Columns | | --------------------------------- | ---------------------- | ---------------------- | | [TRADE\_MAPPING](./trade_mapping) | AS\_OF\_DATE
BOOK | AS\_OF\_DATE
BOOK | ## Populating From a ParentChild Table Instead of creating and populating the BOOK\_HIERARCHY table directly, you can create a BOOK\_PARENT\_CHILD table to store the parent-child relationships. | Column Name | Type | Not Null | Cube Field | Description | | ----------- | ------ | -------- | ---------- | ----------------------------------------------------------------------------- | | NAME | STRING | Y | | Child in the parent-child relationship. | | PARENT | STRING | | | Parent in the parent-child relationship. Null means the child is a root node. | | DATE | DATE | Y | | Timestamp (at close of business) for the data. | With this table and the following SQL script, you can create a series of intermediate views to populate the multiple levels of the BOOK\_HIERARCHY table: ``` /* ------------------------------------------ Create BOOK_HIERARCHY View 1. Create intermediate views (to populate each level of the BOOK_HIERARCHY) 2. Join all intermediate views together ------------------------------------------ */ CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_1 AS SELECT BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL1, NULL AS HIERARCHY_LEVEL2, NULL AS HIERARCHY_LEVEL3, NULL AS HIERARCHY_LEVEL4, NULL AS HIERARCHY_LEVEL5, NULL AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From BOOK_PARENT_CHILD WHERE BOOK_PARENT_CHILD.PARENT IS NULL; CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_2 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_1.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL2, NULL AS HIERARCHY_LEVEL3, NULL AS HIERARCHY_LEVEL4, NULL AS HIERARCHY_LEVEL5, NULL AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_1 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_1.HIERARCHY_LEVEL1) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_1.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_3 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL3, NULL AS HIERARCHY_LEVEL4, NULL AS HIERARCHY_LEVEL5, NULL AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_2 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_2.HIERARCHY_LEVEL2) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_2.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_4 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL4, NULL AS HIERARCHY_LEVEL5, NULL AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_3 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_3.HIERARCHY_LEVEL3) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_3.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_5 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL5, NULL AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_4 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_4.HIERARCHY_LEVEL4) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_4.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_6 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL6, NULL AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_5 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_5.HIERARCHY_LEVEL5) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_5.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_7 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL7, NULL AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_6 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_6.HIERARCHY_LEVEL6) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_6.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_8 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL8, NULL AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_7 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_7.HIERARCHY_LEVEL7) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_7.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_9 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL9, NULL AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_8 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_8.HIERARCHY_LEVEL8) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_8.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_10 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL10, NULL AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_9 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_9.HIERARCHY_LEVEL9) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_9.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_11 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL11, NULL AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_10 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_10.HIERARCHY_LEVEL10) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_10.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_12 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10, INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL12, NULL AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_11 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_11.HIERARCHY_LEVEL11) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_11.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_13 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11, INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL13, NULL AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_12 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_12.HIERARCHY_LEVEL12) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_12.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_14 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12, INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL13 AS HIERARCHY_LEVEL13, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL14, NULL AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_13 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_13.HIERARCHY_LEVEL13) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_13.AS_OF_DATE); CREATE OR REPLACE VIEW INTERMEDIATE_BOOK_HIERARCHY_15 AS SELECT INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL5 AS HIERARCHY_LEVEL5, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL6 AS HIERARCHY_LEVEL6, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL7 AS HIERARCHY_LEVEL7, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL8 AS HIERARCHY_LEVEL8, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL9 AS HIERARCHY_LEVEL9, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL10 AS HIERARCHY_LEVEL10, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL11 AS HIERARCHY_LEVEL11, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL12 AS HIERARCHY_LEVEL12, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL13 AS HIERARCHY_LEVEL13, INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL14 AS HIERARCHY_LEVEL14, BOOK_PARENT_CHILD.NAME AS HIERARCHY_LEVEL15, BOOK_PARENT_CHILD.NAME AS BOOK, BOOK_PARENT_CHILD.DATE AS AS_OF_DATE From INTERMEDIATE_BOOK_HIERARCHY_14 JOIN BOOK_PARENT_CHILD ON (BOOK_PARENT_CHILD.PARENT = INTERMEDIATE_BOOK_HIERARCHY_14.HIERARCHY_LEVEL14) AND (BOOK_PARENT_CHILD.DATE = INTERMEDIATE_BOOK_HIERARCHY_14.AS_OF_DATE); CREATE OR REPLACE VIEW BOOK_HIERARCHY_VIEW AS SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_1 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_2 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_3 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_4 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_5 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_6 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_7 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_8 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_9 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_10 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_11 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_12 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_13 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_14 UNION SELECT * FROM INTERMEDIATE_BOOK_HIERARCHY_15; -- Store the results as a View: CREATE OR REPLACE VIEW BOOK_HIERARCHY AS SELECT * FROM BOOK_HIERARCHY_VIEW; -- Alternatively, store the results as a Table. This table will need to be manually kept up to date, unlike if it were a view: CREATE OR REPLACE TABLE BOOK_HIERARCHY AS SELECT * FROM BOOK_HIERARCHY_VIEW; ``` With this table and the SQL script included in the [online documentation](https://docs.activeviam.com/products/accelerators/frtb/5.1/online-help/database/book_hierarchy.), you can create a series of intermediate views to populate the multiple levels of the BOOK\_HIERARCHY table. For performance reasons, views may impact query and aggregation performance. Injecting the results of the final constructed view into a table can improve query performance. The tradeoff is that this final table will need to be manually kept up to date if the underlying parent-child tables are updated.