Navigation :

BOOK_HIERARCHY

The BOOK_HIERARCHY table contains the multi-level book organizational structure.

note

The content of this table is populated from the content of the BOOK_PARENT_CHILD table.

Column Name	Type	Not Null	Cube Field	Default Value¹	Description
AS_OF_DATE	DATE	Y			Timestamp (at close of business) for the data.
BOOK	STRING	Y		N/A	Leaf node of the book hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15.
BOOK_HIERARCHY_LEVEL1	STRING	Y	Level 1	N/A	Node at level 1 of the book hierarchy.
BOOK_HIERARCHY_LEVEL2	STRING	Y	Level 2	N/A	Node at level 2 of the book hierarchy.
BOOK_HIERARCHY_LEVEL3	STRING	Y	Level 3	N/A	Node at level 3 of the book hierarchy.
BOOK_HIERARCHY_LEVEL4	STRING	Y	Level 4	N/A	Node at level 4 of the book hierarchy.
BOOK_HIERARCHY_LEVEL5	STRING	Y	Level 5	N/A	Node at level 5 of the book hierarchy.
BOOK_HIERARCHY_LEVEL6	STRING	Y	Level 7	N/A	Node at level 6 of the book hierarchy.
BOOK_HIERARCHY_LEVEL7	STRING	Y	Level 6	N/A	Node at level 7 of the book hierarchy.
BOOK_HIERARCHY_LEVEL8	STRING	Y	Level 8	N/A	Node at level 8 of the book hierarchy.
BOOK_HIERARCHY_LEVEL9	STRING	Y	Level 9	N/A	Node at level 9 of the book hierarchy.
BOOK_HIERARCHY_LEVEL10	STRING	Y	Level 10	N/A	Node at level 10 of the book hierarchy.
BOOK_HIERARCHY_LEVEL11	STRING	Y	Level 11	N/A	Node at level 11 of the book hierarchy.
BOOK_HIERARCHY_LEVEL12	STRING	Y	Level 12	N/A	Node at level 12 of the book hierarchy.
BOOK_HIERARCHY_LEVEL13	STRING	Y	Level 13	N/A	Node at level 13 of the book hierarchy.
BOOK_HIERARCHY_LEVEL14	STRING	Y	Level 14	N/A	Node at level 14 of the book hierarchy.
BOOK_HIERARCHY_LEVEL15	STRING	Y	Level 15	N/A	Node at level 15 of the book hierarchy.
DESK	STRING	Y	[Booking].[Desks]	N/A	The desk to which the book belongs. This will match one of the non-_DATAMEMBER_ nodes in levels 1 - 15.
CATEGORY	STRING	Y		N/A	Optional category for the node (and all descendant nodes).

Unique Key

Columns
AS_OF_DATE
BOOK

Incoming Joins

Source Table	Source Columns	Target Columns
TRADE_ATTRIBUTES	AS_OF_DATE BOOK	AS_OF_DATE BOOK

Table creation script

Snowflake

Table creation

create OR REPLACE table BOOK_HIERARCHY(
    AS_OF_DATE DATE NOT NULL,
    BOOK STRING NOT NULL DEFAULT 'N/A',
    DESK STRING NOT NULL DEFAULT 'N/A',
    CATEGORY STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL1 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL2 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL3 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL4 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL5 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL6 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL7 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL8 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL9 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL10 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL11 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL12 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL13 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL14 STRING NOT NULL DEFAULT 'N/A',
    BOOK_HIERARCHY_LEVEL15 STRING NOT NULL DEFAULT 'N/A',
    primary key (AS_OF_DATE, BOOK)
);

Table population from BOOK_PARENT_CHILD table.

insert into BOOK_HIERARCHY
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L1.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, 'N/A') AS DESK,
	ifnull(L1.CATEGORY, 'N/A') AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	'N/A' AS BOOK_HIERARCHY_LEVEL2,
	'N/A' AS BOOK_HIERARCHY_LEVEL3,
	'N/A' AS BOOK_HIERARCHY_LEVLE4,
	'N/A' AS BOOK_HIERARCHY_LEVEL5,
	'N/A' AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L2.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, 'N/A')) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, 'N/A')) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	'N/A' AS BOOK_HIERARCHY_LEVEL3,
	'N/A' AS BOOK_HIERARCHY_LEVLE4,
	'N/A' AS BOOK_HIERARCHY_LEVEL5,
	'N/A' AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L3.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, 'N/A'))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, 'N/A'))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	'N/A' AS BOOK_HIERARCHY_LEVLE4,
	'N/A' AS BOOK_HIERARCHY_LEVEL5,
	'N/A' AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L4.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, 'N/A')))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, 'N/A')))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	'N/A' AS BOOK_HIERARCHY_LEVEL5,
	'N/A' AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L5.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, 'N/A'))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, 'N/A'))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	'N/A' AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L6.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, 'N/A')))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, 'N/A')))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	'N/A' AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L7.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, 'N/A'))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, 'N/A'))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	'N/A' AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L8.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, 'N/A')))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, 'N/A')))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	'N/A' AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L9.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, 'N/A'))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, 'N/A'))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	'N/A' AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L10.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, 'N/A')))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, 'N/A')))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	'N/A' AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L11.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, decode(L11.DESK, 'Y', L11.CHILD, 'N/A'))))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, ifnull(L11.CATEGORY, 'N/A'))))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	L11.CHILD AS BOOK_HIERARCHY_LEVEL11,
	'N/A' AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	inner join BOOK_PARENT_CHILD as L11 on (L10.AS_OF_DATE = L11.AS_OF_DATE and L10.CHILD = L11.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L12.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, decode(L11.DESK, 'Y', L11.CHILD, decode(L12.DESK, 'Y', L12.CHILD, 'N/A')))))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, ifnull(L11.CATEGORY, ifnull(L12.CATEGORY, 'N/A')))))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	L11.CHILD AS BOOK_HIERARCHY_LEVEL11,
	L12.CHILD AS BOOK_HIERARCHY_LEVEL12,
	'N/A' AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	inner join BOOK_PARENT_CHILD as L11 on (L10.AS_OF_DATE = L11.AS_OF_DATE and L10.CHILD = L11.PARENT)
	inner join BOOK_PARENT_CHILD as L12 on (L11.AS_OF_DATE = L12.AS_OF_DATE and L11.CHILD = L12.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L13.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, decode(L11.DESK, 'Y', L11.CHILD, decode(L12.DESK, 'Y', L12.CHILD, decode(L13.DESK, 'Y', L13.CHILD, 'N/A'))))))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, ifnull(L11.CATEGORY, ifnull(L12.CATEGORY, ifnull(L13.CATEGORY, 'N/A'))))))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	L11.CHILD AS BOOK_HIERARCHY_LEVEL11,
	L12.CHILD AS BOOK_HIERARCHY_LEVEL12,
	L13.CHILD AS BOOK_HIERARCHY_LEVEL13,
	'N/A' AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	inner join BOOK_PARENT_CHILD as L11 on (L10.AS_OF_DATE = L11.AS_OF_DATE and L10.CHILD = L11.PARENT)
	inner join BOOK_PARENT_CHILD as L12 on (L11.AS_OF_DATE = L12.AS_OF_DATE and L11.CHILD = L12.PARENT)
	inner join BOOK_PARENT_CHILD as L13 on (L12.AS_OF_DATE = L13.AS_OF_DATE and L12.CHILD = L13.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L14.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, decode(L11.DESK, 'Y', L11.CHILD, decode(L12.DESK, 'Y', L12.CHILD, decode(L13.DESK, 'Y', L13.CHILD, decode(L14.DESK, 'Y', L14.CHILD, 'N/A')))))))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, ifnull(L11.CATEGORY, ifnull(L12.CATEGORY, ifnull(L13.CATEGORY, ifnull(L14.CATEGORY, 'N/A')))))))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	L11.CHILD AS BOOK_HIERARCHY_LEVEL11,
	L12.CHILD AS BOOK_HIERARCHY_LEVEL12,
	L13.CHILD AS BOOK_HIERARCHY_LEVEL13,
	L14.CHILD AS BOOK_HIERARCHY_LEVEL14,
	'N/A' AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	inner join BOOK_PARENT_CHILD as L11 on (L10.AS_OF_DATE = L11.AS_OF_DATE and L10.CHILD = L11.PARENT)
	inner join BOOK_PARENT_CHILD as L12 on (L11.AS_OF_DATE = L12.AS_OF_DATE and L11.CHILD = L12.PARENT)
	inner join BOOK_PARENT_CHILD as L13 on (L12.AS_OF_DATE = L13.AS_OF_DATE and L12.CHILD = L13.PARENT)
	inner join BOOK_PARENT_CHILD as L14 on (L13.AS_OF_DATE = L14.AS_OF_DATE and L13.CHILD = L14.PARENT)
	where L1.PARENT is null
union
select
	L1.AS_OF_DATE AS AS_OF_DATE,
	L15.CHILD AS BOOK,
	decode(L1.DESK, 'Y', L1.CHILD, decode(L2.DESK, 'Y', L2.CHILD, decode(L3.DESK, 'Y', L3.CHILD, decode(L4.DESK, 'Y', L4.CHILD, decode(L5.DESK, 'Y', L5.CHILD, decode(L6.DESK, 'Y', L6.CHILD, decode(L7.DESK, 'Y', L7.CHILD, decode(L8.DESK, 'Y', L8.CHILD, decode(L9.DESK, 'Y', L9.CHILD, decode(L10.DESK, 'Y', L10.CHILD, decode(L11.DESK, 'Y', L11.CHILD, decode(L12.DESK, 'Y', L12.CHILD, decode(L13.DESK, 'Y', L13.CHILD, decode(L14.DESK, 'Y', L14.CHILD, decode(L15.DESK, 'Y', L15.CHILD, 'N/A'))))))))))))))) AS DESK,
	ifnull(L1.CATEGORY, ifnull(L2.CATEGORY, ifnull(L3.CATEGORY, ifnull(L4.CATEGORY, ifnull(L5.CATEGORY, ifnull(L6.CATEGORY, ifnull(L7.CATEGORY, ifnull(L8.CATEGORY, ifnull(L9.CATEGORY, ifnull(L10.CATEGORY, ifnull(L11.CATEGORY, ifnull(L12.CATEGORY, ifnull(L13.CATEGORY, ifnull(L14.CATEGORY, ifnull(L15.CATEGORY, 'N/A'))))))))))))))) AS CATEGORY,
	L1.CHILD AS BOOK_HIERARCHY_LEVEL1,
	L2.CHILD AS BOOK_HIERARCHY_LEVEL2,
	L3.CHILD AS BOOK_HIERARCHY_LEVEL3,
	L4.CHILD AS BOOK_HIERARCHY_LEVLE4,
	L5.CHILD AS BOOK_HIERARCHY_LEVEL5,
	L6.CHILD AS BOOK_HIERARCHY_LEVEL6,
	L7.CHILD AS BOOK_HIERARCHY_LEVEL7,
	L8.CHILD AS BOOK_HIERARCHY_LEVEL8,
	L9.CHILD AS BOOK_HIERARCHY_LEVEL9,
	L10.CHILD AS BOOK_HIERARCHY_LEVEL10,
	L11.CHILD AS BOOK_HIERARCHY_LEVEL11,
	L12.CHILD AS BOOK_HIERARCHY_LEVEL12,
	L13.CHILD AS BOOK_HIERARCHY_LEVEL13,
	L14.CHILD AS BOOK_HIERARCHY_LEVEL14,
	L15.CHILD AS BOOK_HIERARCHY_LEVEL15
	from BOOK_PARENT_CHILD as L1
	inner join BOOK_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT)
	inner join BOOK_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT)
	inner join BOOK_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT)
	inner join BOOK_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT)
	inner join BOOK_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT)
	inner join BOOK_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT)
	inner join BOOK_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT)
	inner join BOOK_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT)
	inner join BOOK_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT)
	inner join BOOK_PARENT_CHILD as L11 on (L10.AS_OF_DATE = L11.AS_OF_DATE and L10.CHILD = L11.PARENT)
	inner join BOOK_PARENT_CHILD as L12 on (L11.AS_OF_DATE = L12.AS_OF_DATE and L11.CHILD = L12.PARENT)
	inner join BOOK_PARENT_CHILD as L13 on (L12.AS_OF_DATE = L13.AS_OF_DATE and L12.CHILD = L13.PARENT)
	inner join BOOK_PARENT_CHILD as L14 on (L13.AS_OF_DATE = L14.AS_OF_DATE and L13.CHILD = L14.PARENT)
	inner join BOOK_PARENT_CHILD as L15 on (L14.AS_OF_DATE = L15.AS_OF_DATE and L14.CHILD = L15.PARENT)
	where L1.PARENT is null;

If the default value is marked as empty, it means that the default value is 'null' for nullable fields, and that a value needs to be explicitly set for non-nullable fields. ↩︎