-- DROP VIEW V_ENTITY_HIERARCHY;
CREATE VIEW V_ENTITY_HIERARCHY
AS
WITH RECURSIVE AllEntity(EntityID, BaseEntityIndex, FullName, Depth)
AS 
(
	SELECT 
		e.EntityID
	,	e.EntityID as BaseEntity
	,	e.Schema || '.' || e.EntName
	,	0 as Depth
	FROM VCLib.T_ENTITIES e	
	UNION ALL	
	SELECT 
		e.EntityID
	,	e2.BaseEntityIndex
	,	e.Schema || '.' || e.EntName
	,	Depth + 1
	FROM VCLib.T_ENTITIES e
	INNER JOIN 
		AllEntity e2 
		ON e.BaseEntity = e2.EntityID
)
SELECT 
	EntityID
,	BaseEntityIndex as BaseEntity
,	FullName
,	Depth
FROM AllEntity;