SQL for task relationship

Hi all,

I want to have an extract of the task relationship in JDE 9.1 by SQL. The extract should provide me with the parent task and its subsequent childs. The table F9001 only give the BLOB - Hexadecimal type data for the parent - child relationship. The extract should contain the name of tasks instead of the Hexadecimal. Can you please provide me with the SQL?

Thanks in advance.
---------------------------------
Developer
JD Edwards EnterpriseOne 9.1
Oracle 11g

avishek,

There are no blob columns in either the Task Master table (F9000) or the Task Relationships tale (F9001). The link between the two tables is the internal task id (an alphanumeric column, not hexadecimal) (F9000.TMTASKID; F9001.TRPARNTTSK; F9001.TRCHILDTSK).

Using the F9001 as the "from" table, retrieve the parent and child details from F9000 using sub-queries in the select clause.

For example (not to give you a fish but teach you how to fish - see the quote in my signature):

SELECT TRRLTYPE, (SELECT TMLNGTASK FROM F9001 WHERE TMTASKID=TRPARNTTSK) PARENT_DESCRIPTION,
TRPRSSEQ, (SELECT TMLNGTASK FROM F9001 WHERE TMTASKID=TRCHILDTSK) CHILD_DESCRIPTION
FROM F9000
WHERE ...

I don't have the SQL, but I wrote the equivalent as a UBE so it shows the parent/tasks underneath it (up to 10 levels deep). It is basically just a bunch of recursive calls..

Hello here's my SQL based on a dedicated menu (B.TRRLTYPE='100') :
SELECT A.TMTASKID,A.TMTASKNM,A.TMLNGTASK,A.TMOBNM,A.TMVER,B.TRPRSSEQ
,C.TMTASKNM,C.TMLNGTASK,C.TMOBNM,C.TMVER,D.TRPRSSEQ --2 NIV
,E.TMTASKNM,E.TMLNGTASK,E.TMOBNM,E.TMVER,F.TRPRSSEQ --3 NIV
,G.TMTASKNM,G.TMLNGTASK,G.TMOBNM,G.TMVER,H.TRPRSSEQ --4 NIV
,I.TMTASKNM,I.TMLNGTASK,I.TMOBNM,I.TMVER,J.TRPRSSEQ --5 NIV
,K.TMTASKNM,K.TMLNGTASK,K.TMOBNM,K.TMVER,L.TRPRSSEQ --6 NIV
,M.TMTASKNM,M.TMLNGTASK,M.TMOBNM,M.TMVER,N.TRPRSSEQ --7 NIV
,O.TMTASKNM,O.TMLNGTASK,O.TMOBNM,O.TMVER,P.TRPRSSEQ --8 NIV
,Q.TMTASKNM,Q.TMLNGTASK,Q.TMOBNM,Q.TMVER,R.TRPRSSEQ --9 NIV
,S.TMTASKNM,S.TMLNGTASK,S.TMOBNM,S.TMVER,T.TRPRSSEQ --10 NIV
,U.TMTASKNM,U.TMLNGTASK,U.TMOBNM,U.TMVER,V.TRPRSSEQ --11 NIV
,W.TMTASKNM,W.TMLNGTASK,W.TMOBNM,W.TMVER,X.TRPRSSEQ --12 NIV
FROM PRODCTL.F9000 A,PRODCTL.F9001 B
LEFT JOIN PRODCTL.F9000 C ON C.TMTASKID=B.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 D ON D.TRPARNTTSK=C.TMTASKID
LEFT JOIN PRODCTL.F9000 E ON E.TMTASKID=D.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 F ON F.TRPARNTTSK=E.TMTASKID
LEFT JOIN PRODCTL.F9000 G ON G.TMTASKID=F.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 H ON H.TRPARNTTSK=G.TMTASKID
LEFT JOIN PRODCTL.F9000 I ON I.TMTASKID=H.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 J ON J.TRPARNTTSK=I.TMTASKID
LEFT JOIN PRODCTL.F9000 K ON K.TMTASKID=J.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 L ON L.TRPARNTTSK=K.TMTASKID
LEFT JOIN PRODCTL.F9000 M ON M.TMTASKID=L.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 N ON N.TRPARNTTSK=M.TMTASKID
LEFT JOIN PRODCTL.F9000 O ON O.TMTASKID=N.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 P ON P.TRPARNTTSK=O.TMTASKID
LEFT JOIN PRODCTL.F9000 Q ON Q.TMTASKID=P.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 R ON R.TRPARNTTSK=Q.TMTASKID
LEFT JOIN PRODCTL.F9000 S ON S.TMTASKID=R.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 T ON T.TRPARNTTSK=S.TMTASKID
LEFT JOIN PRODCTL.F9000 U ON U.TMTASKID=T.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 V ON V.TRPARNTTSK=U.TMTASKID
LEFT JOIN PRODCTL.F9000 W ON W.TMTASKID=V.TRCHILDTSK
LEFT JOIN PRODCTL.F9001 X ON X.TRPARNTTSK=W.TMTASKID
WHERE A.TMTASKID=B.TRPARNTTSK AND B.TRRLTYPE=A.TMTASKID AND B.TRRLTYPE='100'--AND X.TRPRSSEQ>1
ORDER BY B.TRPRSSEQ,D.TRPRSSEQ,F.TRPRSSEQ,H.TRPRSSEQ,J.TRPRSSEQ,L.TRPRSSEQ,N.TRPRSSEQ,P.TRPRSSEQ,R.TRPRSSEQ,T.TRPRSSEQ,V.TRPRSSEQ,X.TRPRSSEQ


Regards and have fun