Parent-Child table for OBIEE 11-12 on MS SQL server

Script for generation data of parent-child relationship table


CREATE TABLE [dbo].[PC_FIN_CFO](
[MEMBER_KEY] varchar(36) NOT NULL,
[ANCESTOR_KEY] varchar(36) NULL,
[DISTANCE] [int] NULL,
[IS_LEAF] [int] NULL
) ON [PRIMARY]

/**********************************************************/

delete from ALT_PC_FIN_CFO;

DECLARE @id varchar(36);

DECLARE db_cursor CURSOR FOR
select row_wid from DC_FIN_CFO

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
--------------

WITH tblParent AS
(
    SELECT DC_FIN_CFO.*, 0 as generation,/*
ISNULL((select 1
where not exists (
select 'x' from DC_FIN_CFO where
parent_row_wid=@id)),0) */ 0 isleaf
,@id START_ROW_WID
        FROM DC_FIN_CFO WHERE ROW_WID = @id
    UNION ALL
    SELECT DC_FIN_CFO.*, generation+1,
ISNULL((select 1
where not exists (
select 'x' from DC_FIN_CFO where
parent_row_wid=@id)),0) isleaf
,@id START_ROW_WID
        FROM DC_FIN_CFO  JOIN tblParent  ON DC_FIN_CFO.ROW_WID = tblParent.PARENT_ROW_WID
)
insert into ALT_PC_FIN_CFO(MEMBER_KEY,ANCESTOR_KEY,DISTANCE,IS_LEAF)
SELECT START_ROW_WID, ROW_WID, generation, isleaf  FROM  tblParent
OPTION(MAXRECURSION 32767)

--------------
FETCH NEXT FROM db_cursor INTO @id
--------------
END

CLOSE db_cursor
DEALLOCATE db_cursor


insert into ALT_PC_FIN_CFO(MEMBER_KEY,ANCESTOR_KEY,DISTANCE,IS_LEAF)
select ROW_WID, PARENT_ROW_WID, null, 0 from DC_FIN_CFO where PARENT_ROW_WID is null;