Skip to main content

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.

The BOOK_HIERARCHY table contains the multi-level book organizational structure and the desk-level information.
Column NameTypeNot NullCube FieldDescription
AS_OF_DATEDATEYTimestamp (at close of business) for the data.
BOOKSTRINGYLeaf node of the book hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15.
HIERARCHY_LEVEL1STRINGLevel 1Node at level 1 of the book hierarchy.
HIERARCHY_LEVEL2STRINGLevel 2Node at level 2 of the book hierarchy.
HIERARCHY_LEVEL3STRINGLevel 3Node at level 3 of the book hierarchy.
HIERARCHY_LEVEL4STRINGLevel 4Node at level 4 of the book hierarchy.
HIERARCHY_LEVEL5STRINGLevel 5Node at level 5 of the book hierarchy.
HIERARCHY_LEVEL6STRINGLevel 7Node at level 6 of the book hierarchy.
HIERARCHY_LEVEL7STRINGLevel 6Node at level 7 of the book hierarchy.
HIERARCHY_LEVEL8STRINGLevel 8Node at level 8 of the book hierarchy.
HIERARCHY_LEVEL9STRINGLevel 9Node at level 9 of the book hierarchy.
HIERARCHY_LEVEL10STRINGLevel 10Node at level 10 of the book hierarchy.
HIERARCHY_LEVEL11STRINGLevel 11Node at level 11 of the book hierarchy.
HIERARCHY_LEVEL12STRINGLevel 12Node at level 12 of the book hierarchy.
HIERARCHY_LEVEL13STRINGLevel 13Node at level 13 of the book hierarchy.
HIERARCHY_LEVEL14STRINGLevel 14Node at level 14 of the book hierarchy.
HIERARCHY_LEVEL15STRINGLevel 15Node at level 15 of the book hierarchy.

​
Unique Key

Columns
AS_OF_DATE
BOOK

​
Incoming Joins

Source TableSource ColumnsTarget Columns
TRADE_MAPPINGAS_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 NameTypeNot NullCube FieldDescription
NAMESTRINGYChild in the parent-child relationship.
PARENTSTRINGParent in the parent-child relationship. Null means the child is a root node.
DATEDATEYTimestamp (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, 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.