COUNTERPARTY_HIERARCHY
The COUNTERPARTY_HIERARCHY table contains the multi-level counterparty organizational structure.
note
Note: The content of this table is populated from the content of the COUNTERPARTY_PARENT_CHILD table.
| Column Name | Type | Not Null | Cube Field | Default Value1 | Description |
|---|---|---|---|---|---|
| AS_OF_DATE | DATE | Y | Timestamp (at close of business) for the data. | ||
| COUNTERPARTY_ID | STRING | Y | [Counterparties].[CounterpartyIds] | N/A | Leaf node of the book hierarchy. This matches the last non-_DATAMEMBER_ node in levels 1 - 15. |
| COUNTERPARTY_HIERARCHY_LEVEL1 | STRING | Y | Level 1 | N/A | Node at level 1 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL2 | STRING | Y | Level 2 | N/A | Node at level 2 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL3 | STRING | Y | Level 3 | N/A | Node at level 3 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL4 | STRING | Y | Level 4 | N/A | Node at level 4 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL5 | STRING | Y | Level 5 | N/A | Node at level 5 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL6 | STRING | Y | Level 6 | N/A | Node at level 6 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL7 | STRING | Y | Level 7 | N/A | Node at level 7 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL8 | STRING | Y | Level 8 | N/A | Node at level 8 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL9 | STRING | Y | Level 9 | N/A | Node at level 9 of the counterparty hierarchy. |
| COUNTERPARTY_HIERARCHY_LEVEL10 | STRING | Y | Level 10 | N/A | Node at level 10 of the counterparty hierarchy. |
Unique Key
| Columns |
|---|
| AS_OF_DATE |
| COUNTERPARTY_ID |
Incoming Joins
| Source Table | Source Columns | Target Columns |
|---|---|---|
| TRADE_ATTRIBUTES | AS_OF_DATE COUNTERPARTY_ID |
AS_OF_DATE COUNTERPARTY_ID |
Table creation script
Snowflake
Table creation
create OR REPLACE table COUNTERPARTY_HIERARCHY(
AS_OF_DATE DATE NOT NULL,
COUNTERPARTY_ID STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL1 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL2 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL3 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL4 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL5 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL6 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL7 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL8 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL9 STRING NOT NULL DEFAULT 'N/A',
COUNTERPARTY_HIERARCHY_LEVEL10 STRING NOT NULL DEFAULT 'N/A',
primary key (AS_OF_DATE, COUNTERPARTY_ID)
);
Table population from COUNTERPARTY_PARENT_CHILD table
insert into COUNTERPARTY_HIERARCHY
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L1.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL2,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL3,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL4,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL5,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L2.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL3,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL4,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL5,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L3.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL4,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL5,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L4.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL5,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L5.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L6.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
C6.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL6,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT and L6.CHILD != L6.PARENT) inner join COUNTERPARTIES as C6 on (L6.AS_OF_DATE = C6.AS_OF_DATE and L6.CHILD = C6.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L7.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
C6.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL6,
C7.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL7,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT and L6.CHILD != L6.PARENT) inner join COUNTERPARTIES as C6 on (L6.AS_OF_DATE = C6.AS_OF_DATE and L6.CHILD = C6.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT and L7.CHILD != L7.PARENT) inner join COUNTERPARTIES as C7 on (L7.AS_OF_DATE = C7.AS_OF_DATE and L7.CHILD = C7.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L8.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
C6.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL6,
C7.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL7,
C8.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL8,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT and L6.CHILD != L6.PARENT) inner join COUNTERPARTIES as C6 on (L6.AS_OF_DATE = C6.AS_OF_DATE and L6.CHILD = C6.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT and L7.CHILD != L7.PARENT) inner join COUNTERPARTIES as C7 on (L7.AS_OF_DATE = C7.AS_OF_DATE and L7.CHILD = C7.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT and L8.CHILD != L8.PARENT) inner join COUNTERPARTIES as C8 on (L8.AS_OF_DATE = C8.AS_OF_DATE and L8.CHILD = C8.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L9.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
C6.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL6,
C7.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL7,
C8.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL8,
C9.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL9,
'N/A' AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT and L6.CHILD != L6.PARENT) inner join COUNTERPARTIES as C6 on (L6.AS_OF_DATE = C6.AS_OF_DATE and L6.CHILD = C6.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT and L7.CHILD != L7.PARENT) inner join COUNTERPARTIES as C7 on (L7.AS_OF_DATE = C7.AS_OF_DATE and L7.CHILD = C7.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT and L8.CHILD != L8.PARENT) inner join COUNTERPARTIES as C8 on (L8.AS_OF_DATE = C8.AS_OF_DATE and L8.CHILD = C8.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT and L9.CHILD != L9.PARENT) inner join COUNTERPARTIES as C9 on (L9.AS_OF_DATE = C9.AS_OF_DATE and L9.CHILD = C9.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD
union
select distinct
L1.AS_OF_DATE AS AS_OF_DATE,
L10.CHILD AS COUNTERPARTY_ID,
C1.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL1,
C2.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL2,
C3.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL3,
C4.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL4,
C5.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL5,
C6.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL6,
C7.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL7,
C8.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL8,
C9.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL9,
C10.COUNTERPARTY_NAME AS COUNTERPARTY_HIERARCHY_LEVEL10
from COUNTERPARTY_PARENT_CHILD as L1 inner join COUNTERPARTIES as C1 on (L1.AS_OF_DATE = C1.AS_OF_DATE and L1.CHILD = C1.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L2 on (L1.AS_OF_DATE = L2.AS_OF_DATE and L1.CHILD = L2.PARENT and L2.CHILD != L2.PARENT) inner join COUNTERPARTIES as C2 on (L2.AS_OF_DATE = C2.AS_OF_DATE and L2.CHILD = C2.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L3 on (L2.AS_OF_DATE = L3.AS_OF_DATE and L2.CHILD = L3.PARENT and L3.CHILD != L3.PARENT) inner join COUNTERPARTIES as C3 on (L3.AS_OF_DATE = C3.AS_OF_DATE and L3.CHILD = C3.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L4 on (L3.AS_OF_DATE = L4.AS_OF_DATE and L3.CHILD = L4.PARENT and L4.CHILD != L4.PARENT) inner join COUNTERPARTIES as C4 on (L4.AS_OF_DATE = C4.AS_OF_DATE and L4.CHILD = C4.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L5 on (L4.AS_OF_DATE = L5.AS_OF_DATE and L4.CHILD = L5.PARENT and L5.CHILD != L5.PARENT) inner join COUNTERPARTIES as C5 on (L5.AS_OF_DATE = C5.AS_OF_DATE and L5.CHILD = C5.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L6 on (L5.AS_OF_DATE = L6.AS_OF_DATE and L5.CHILD = L6.PARENT and L6.CHILD != L6.PARENT) inner join COUNTERPARTIES as C6 on (L6.AS_OF_DATE = C6.AS_OF_DATE and L6.CHILD = C6.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L7 on (L6.AS_OF_DATE = L7.AS_OF_DATE and L6.CHILD = L7.PARENT and L7.CHILD != L7.PARENT) inner join COUNTERPARTIES as C7 on (L7.AS_OF_DATE = C7.AS_OF_DATE and L7.CHILD = C7.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L8 on (L7.AS_OF_DATE = L8.AS_OF_DATE and L7.CHILD = L8.PARENT and L8.CHILD != L8.PARENT) inner join COUNTERPARTIES as C8 on (L8.AS_OF_DATE = C8.AS_OF_DATE and L8.CHILD = C8.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L9 on (L8.AS_OF_DATE = L9.AS_OF_DATE and L8.CHILD = L9.PARENT and L9.CHILD != L9.PARENT) inner join COUNTERPARTIES as C9 on (L9.AS_OF_DATE = C9.AS_OF_DATE and L9.CHILD = C9.COUNTERPARTY_ID)
inner join COUNTERPARTY_PARENT_CHILD as L10 on (L9.AS_OF_DATE = L10.AS_OF_DATE and L9.CHILD = L10.PARENT and L10.CHILD != L10.PARENT) inner join COUNTERPARTIES as C10 on (L10.AS_OF_DATE = C10.AS_OF_DATE and L10.CHILD = C10.COUNTERPARTY_ID)
where L1.PARENT is null or L1.PARENT = L1.CHILD;
-
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. ↩︎