LEGAL_ENTITY_HIERARCHY

/*
------------------------------------------
    Create LEGAL_ENTITY_HIERARCHY View

    1. Create intermediate views (to populate each level of the LEGAL_ENTITY_HIERARCHY)
    2. Join all intermediate views together
------------------------------------------
*/

CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL1,
	NULL                           AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From LEGAL_ENTITY_PARENT_CHILD
WHERE LEGAL_ENTITY_PARENT_CHILD.PARENT IS NULL;


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL2,
	NULL                           AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.HIERARCHY_LEVEL1) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL3,
	NULL                           AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.HIERARCHY_LEVEL2) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL4,
	NULL                           AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.HIERARCHY_LEVEL3) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3.AS_OF_DATE);


CREATE OR REPLACE VIEW INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_5 AS
SELECT
	LEGAL_ENTITY_PARENT_CHILD.DATE AS AS_OF_DATE,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS LEGAL_ENTITY,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL1 AS HIERARCHY_LEVEL1,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL2 AS HIERARCHY_LEVEL2,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL3 AS HIERARCHY_LEVEL3,
	INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL4 AS HIERARCHY_LEVEL4,
	LEGAL_ENTITY_PARENT_CHILD.NAME AS HIERARCHY_LEVEL5
From INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4
JOIN LEGAL_ENTITY_PARENT_CHILD ON (LEGAL_ENTITY_PARENT_CHILD.PARENT = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.HIERARCHY_LEVEL4) AND (LEGAL_ENTITY_PARENT_CHILD.DATE = INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4.AS_OF_DATE);


// Join all intermediate views together
CREATE OR REPLACE VIEW LEGAL_ENTITY_HIERARCHY_VIEW AS
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_1 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_2 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_3 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_4 UNION
    SELECT * FROM INTERMEDIATE_LEGAL_ENTITY_HIERARCHY_5;


-- Store the results as a View:
CREATE OR REPLACE VIEW LEGAL_ENTITY_HIERARCHY AS SELECT * FROM LEGAL_ENTITY_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 LEGAL_ENTITY_HIERARCHY AS SELECT * FROM LEGAL_ENTITY_HIERARCHY_VIEW;