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.
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?
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)