Jeffelim
Member
Hello,
I'm trying to export all of the menu path from JDE XE.
I found a query in this forum that part of it is working, the other part is not.
WITH
Tops AS (
SELECT
*
FROM PRODCTL.F9000
WHERE TMTASKID IN ('100','110','120','130','140') -- Only process these top-level menu items
),
Details AS (
SELECT
*
FROM
PRODCTL.F9000 LEFT JOIN PRODCTL.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
The error starts with the "1 as level" in "Tree as".
Can somebody help me, please?
I'm trying to export all of the menu path from JDE XE.
I found a query in this forum that part of it is working, the other part is not.
WITH
Tops AS (
SELECT
*
FROM PRODCTL.F9000
WHERE TMTASKID IN ('100','110','120','130','140') -- Only process these top-level menu items
),
Details AS (
SELECT
*
FROM
PRODCTL.F9000 LEFT JOIN PRODCTL.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
The error starts with the "1 as level" in "Tree as".
Can somebody help me, please?