I have some data on one table that is heirerchical, i.e. there is a PARENT column that stores a number which is the unique ID of another record in the same table.
There is more than two levels, and for each record I need to display the full path - not just the immediate parent.
Every time I need to do this, I have to trawl through blogs/forums to get the syntax right.
I am creating a functionality within an application for employees to add their skills & expertise to their resource profile.
I am restricting their choices to a list of predefined data in a table called RES_LOV_SKILLS
In the front end of the application, I have a page which allows admins to manage this list. When they add a new skill, I want them to be able to choose a parent, and when doing so, enable them to see the full path of the skill (...you will see what I mean in a moment).
For more info see Oracle Documentation:
Using this query I was able to print the full category of each skill/expertise:
substr(sys_connect_by_path(expertise,' > '), 4) as d,
start with expertise_parent IS NULL
connect by prior id = expertise_parent;
The results being:
Now I just create an LOV based on these values (the full_path being the display value, and returning the ID as the return value) and I can use it for my users to choose a parent for their new skill and keep them nicely categorised!