AnsweredAssumed Answered

Hierarchial Query Example

Question asked by rkljr on Nov 19, 2008
Latest reply on Dec 9, 2008 by Naman_Patel
I would like to create a hierarchial query of projects such that one can drill down/expand each department and quarter. Thus, I would like a listing of departments and be able to expand each department and see the quarters within which projects are starting and then expand the quarter to see the projects with their scheduled start date. I have looked in the Studio guide but it does not have a good example of a hierarchial query. I have also looked at some of the queries that are part of Clarity but it is hard to devine the pattern as they are more complex. My query is very simple but I am not sure how to represent the multiple sections in NSQL. Do I use a UNION to represent each level and pass the hg_row_id as required? The SQL for the data that I want is:  SELECT i.code,     i.NAME,     prjOBS(i.code) projectOBS,     To_Char(i.schedule_start,'Q') scheduleStartQtr,     i.schedule_startFROM inv_investments i WHERE i.is_active = 1 The prjOBS function returns the full path of the project OBS (i.e. /All Projects/IS Projects/IS Business Management).