WITH
Tops AS (
SELECT
*
FROM TESTCTL.F9000
WHERE TMTASKID IN ('100','110','120','130','140') -- Only process these top-level menu items
),
Details AS (
SELECT
*
FROM
TESTCTL.F9000 LEFT JOIN TESTCTL.F9001 ON TMTASKID = TRCHILDTSK
),
Tree AS (
SELECT
Tops.TMTASKID as TopLevelTaskID,
Details.TRPARNTTSK as ParentID,
Details.TRCHILDTSK as ChildID,
1 as Level,
CAST(RTRIM(Tops.TMLNGTASK) + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)) as FullPathDesc,
RTRIM(Details.TMLNGTASK) as TaskName,
CAST(CONCAT(RTRIM(Tops.TMTASKID), '|', RIGHT(CONCAT('0000',Details.TRPRSSEQ),4), '|') as VARCHAR(40)) as SortOrder,
CASE WHEN Details.TMTASKTYPE = '07' THEN 'Folder' ELSE 'Item' END as ItemType,
Details.TMOBNM,
Details.TMVER,
Details.TMFMNM
FROM
Tops LEFT JOIN Details ON Tops.TMTASKID = Details.TRPARNTTSK
UNION ALL
SELECT
Tree.TopLevelTaskID,
Details.TRPARNTTSK,
Details.TRCHILDTSK,
Tree.Level + 1,
CAST( Tree.FullPathDesc + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)),
RTRIM(Details.TMLNGTASK),
CAST( CONCAT(Tree.SortOrder, RIGHT(CONCAT('0000',Details.TRPRSSEQ),4)) AS VARCHAR(40)),
CASE WHEN Details.TMTASKTYPE = '07' THEN 'Folder' ELSE 'Item' END as ItemType,
Details.TMOBNM,
Details.TMVER,
Details.TMFMNM
FROM
Tree INNER JOIN Details ON Tree.ChildID = Details.TRPARNTTSK
),
Unioned AS (
SELECT
FullPathDesc,
ItemType,
SortOrder,
TMOBNM,
TMVER,
TMFMNM
FROM Tree
UNION ALL
SELECT
TMLNGTASK,
'Folder',
CONCAT(RTRIM(TMTASKID),'|'),
'',
'',
''
FROM Tops
)
SELECT *, CONCAT(RTRIM(TMOBNM),'|',RTRIM(TMFMNM),'|',RTRIM(TMVER)) as FastPath FROM Unioned
ORDER BY SortOrder