Select T_TASK.ASSIGNED_TO for multiples

, , ,
1

Hi
I am using the following SQL to get back a list of tasks, this worked whilst we only had 1 ID in the ASSIGNED_TO column but now there are up to 2 I cannot get it to return both.
PROCESS_INSTANCE_DATA & STUD_CRSE_YEAR are our own data tables and can be ignored, the problem is in selecting the THINGS.ITEMNAME AS WORKQUEUE to return both ID’s that match THINGS.IDTHING = TT.ASSIGNED_TO. This line has been commented out & replaced by the regexp_substr to match with the first ID only so the SQL continues working.
To be clear TT.ASSIGNED_TO is a comma separated list of ID’s.
Can anybody help?
Thanks

SELECT DISTINCT
       TT.CUSTOM_TASK_ID,
       TT.TASK_ID,
       TT.TASK_NAME,
       THINGS.ITEMNAME AS WORKQUEUE, 
       ttn.itemname ACCEPTED_BY
  FROM T_TASK TT,
       PROCESS_INSTANCE_DATA PID,
       PRA_PROCESS_STEP PRO,
       TBLUSER TU,
       STUD_CRSE_YEAR SCY,
       TBLTHINGNAME THINGS,
       TBLTHINGNAME ttn
 WHERE TT.STATUS = 1
       AND THINGS.IDTHING IN (SELECT regexp_substr( TT.ASSIGNED_TO, '[[:alnum:]]+') regexp_substr FROM dual)
--       AND THINGS.IDTHING = TT.ASSIGNED_TO
       AND TT.PRT_PROCESS_MODEL_ID = 'CorrespondenceBPM/Correspondence'
       AND TT.ACCEPTED_BY = ttn.IDTHING(+)
       AND TT.PRT_PROCESS_INSTANCE_ID = PID.PROCESS_ID
       AND PID.PROCESS_BPM = 'CorrespondenceBPM'
       AND PID.PROCESS_ID = PRO.INSTANCEID
       AND PID.STUD_REF_NO = SCY.STUD_REF_NO
       AND SCY.LATEST_CRSE_IND = 'Y'
       AND SCY.SESSION_CODE = '2021'        
       AND PRO.AUDITTIMESTAMP = (SELECT MAX (PRO1.AUDITTIMESTAMP)
                                   FROM PRA_PROCESS_STEP PRO1
                                  WHERE PRO1.INSTANCEID = PRO.INSTANCEID)
2

Can you please explain the use case? There are services and APIs provided to search tasks and not using SQL queries directly.

1 Like
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.