ITG Plus: Tips & Tricks

A definitive resource for for ITG developers. Check out the nitty gritties of ITG in simple words and learn them through simple examples.

My Photo
Name:
Location: India

A software professional from India with 16+ years of IT experience. Worked on diverse technologies from various vendors, such as Microsoft technologies, IBM groupware, Project and Portfolio Management, Database Systems, Configuration Management, Document Management etc. In my leisure time, I like to share ideas. Hence this blog. Find out more about me. Important note: Ideas and opinion expressed in this blog are strictly my personal opinion and in no way represent the viewpoint of my employer and vice-versa. All content here must be seen in my individual capacity.

Thursday, January 25, 2007

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