Menu Path

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?
 
Jeffelin, if you're using SQL Server or Oracle database, to avoid using reserved words try renaming the as-name "Level" to something else.
 
I'm using Toad.

At the beginning the error is in this two sentences.

1 as Level,
CAST(RTRIM(Tops.TMLNGTASK) + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)) as FullPathDesc,

If I remove the "1as Level", I get the error with this:
CAST(RTRIM(Tops.TMLNGTASK) + ' | ' + RTRIM(Details.TMLNGTASK) AS VARCHAR(1000)) as FullPathDesc,

Any help?

Thank you.
 
Hello.

This is the information about the version:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Thank you.
 
Back
Top