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)

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