List the sub project names

Did you ever need to list down the Project/Subprojects that are a part of a particular Project Plan? The problem while dealing with this issue is that you do not know in advance how many levels of nesting of Project/Subproject/Task level there might be. Well, fear not, DECODE long live!

But there are more challenges. Including but not limited to, finding the start and end positions of the substring that you want to extract. Well, if that's what you came here looking for, here is your solution. And if you are not looking for it, save it in your scrap book for your future use. Because the day you need it, you will certainly realize its utility.


The code:

---

SELECT decode(instr(project_path_name_list,   '>',   1,   2),   0,
SUBSTR(project_path_name_list,   instr(project_path_name_list,   '>') + 2),
SUBSTR(SUBSTR(project_path_name_list,   instr(project_path_name_list,   '>') + 2),   0,
       ((instr(project_path_name_list,   '>',   1,   2) -(instr(project_path_name_list,   '>')) -2)
     )
)
)
FROM kdrv_projects_v
WHERE master_project_name = 'New Project'
AND project_type_code = 'TASK'

---

Just replace the project name with the one you want and you are ready to go!!!

- Ranjeet Rain