redshift
CREATE OR REPLACE view enriched_stage_ersdb.v_TCFG_CCHierarchy
as
with cteInit
as
(
SELECT
H.HierarchyCode AS CCCode
,ISNULL(CC.Name, H.Description) AS CCName
, H.ParentCode AS CCParentCode,
H.CreateTime as CreateTime
FROM enriched_PRESTAGE_ERSDB.TCFG_CCHierarchy AS H LEFT OUTER JOIN
enriched_PRESTAGE_ERSDB.TCFG_CostCenter AS CC
ON H.HierarchyCode = CC.Code AND H.ParentCode = CC.HierarchyCode
),
cte_1
as
(
select *,1 as HierarchyLevel from cteInit where CCParentCode is null
),
cte_2
as
(
select cteInit.*, 2 from cteInit
inner join cte_1 on cteInit.CCParentCode=cte_1.CCCode
)
,
cte_3
as
(
select cteInit.*, 3 from cteInit
inner join cte_2 on cteInit.CCParentCode=cte_2.CCCode
)
,
cte_4
as
(
select cteInit.*, 4 from cteInit
inner join cte_3 on cteInit.CCParentCode=cte_3.CCCode
)
,
cte_5
as
(
select cteInit.*, 5 from cteInit
inner join cte_4 on cteInit.CCParentCode=cte_4.CCCode
)
,
cte_6
as
(
select cteInit.*, 6 from cteInit
inner join cte_5 on cteInit.CCParentCode=cte_5.CCCode
)
,
cte_7
as
(
select cteInit.*, 7 from cteInit
inner join cte_6 on cteInit.CCParentCode=cte_6.CCCode
)
,
cte_8
as
(
select cteInit.*, 8 from cteInit
inner join cte_7 on cteInit.CCParentCode=cte_7.CCCode
)
,
cte_9
as
(
select *,1 as HierarchyLevel from cteInit where CCParentCode is null
union all
select cteInit.*, 2 from cteInit
inner join cte_1 on cteInit.CCParentCode=cte_1.CCCode
union all
select cteInit.*, 3 from cteInit
inner join cte_2 on cteInit.CCParentCode=cte_2.CCCode
union all
select cteInit.*, 4 from cteInit
inner join cte_3 on cteInit.CCParentCode=cte_3.CCCode
union all
select cteInit.*, 5 from cteInit
inner join cte_4 on cteInit.CCParentCode=cte_4.CCCode
union all
select cteInit.*, 6 from cteInit
inner join cte_5 on cteInit.CCParentCode=cte_5.CCCode
union all
select cteInit.*, 7 from cteInit
inner join cte_6 on cteInit.CCParentCode=cte_6.CCCode
union all
select cteInit.*, 8 from cteInit
inner join cte_7 on cteInit.CCParentCode=cte_7.CCCode
union all
select cteInit.*, 9 from cteInit
inner join cte_8 on cteInit.CCParentCode=cte_8.CCCode
)
select * from cte_9;
sql server
CREATE view [eRSDB].[v_TCFG_CCHierarchy]
as
with cteInit
as
(SELECT H.HierarchyCode AS CCCode,ISNULL(CC.Name, H.Description) AS CCName
, H.ParentCode AS CCParentCode,
H.CreateTime as CreateTime
FROM [PRESTAGE].ERSDB.TCFG_CCHierarchy AS H LEFT OUTER JOIN
[PRESTAGE].ERSDB.TCFG_CostCenter AS CC ON H.HierarchyCode = CC.Code AND H.ParentCode = CC.HierarchyCode
),
cte
as
(
select *,1 as [HierarchyLevel] from cteInit where CCParentCode is null
union all
select cteInit.*,[HierarchyLevel] +1 from cteInit inner join cte on cteInit.CCParentCode=cte.CCCode
)
select * from cte