question

Hi dears,
I am developing reports on a Project Management DB (Planview's E1). My tables are about tasks in WBS and their parameters, resources that execute them etc...

There is a 0-N relation between tasks & one of the parameters (wbs32).
I can easily get a result where I see every task of a project ordered by the WBS hierarchical order and the corresponding values of the wbs32 parameter.

select
sm.map_code,
sl.name Level,
(select description from ip.structure where structure_code=pe.planning_code) Nom,
(select description from ip.structure where structure_code=ma.attribute_code) Application,
ma.numeric_value
from ip.planning_entity pe
join ip.structure_map sm on pe.planning_code=sm.structure_code
join ip.structure_level sl on sm.depth=sl.level_num
left join ip.multi_attribute ma on pe.planning_code=ma.prime_structure_code and ma.alt_structure_name='Wbs32'
where pe.ppl_code='46925' and pe.ppl_code <> pe.planning_code

But I am requested to "top-heritate" the values if a task has no associated values for the parameter, going one level higher until I find something or until I get to the project level (called ppl).

I suppose I have to create a recursive table function but I don't know how to do.

I could only make the simple function for one task code.

ALTER FUNCTION zz_GetPlanningEntityApplications (
@planning_code CHAR(10)
)
RETURNS TABLE
AS
RETURN
select
sm.map_code,
sl.name Level,
pe.planning_code,
(select description from ip.structure where structure_code=pe.planning_code) Nom,
(select description from ip.structure where structure_code=ma.attribute_code) Application,
ma.numeric_value Percentage
from ip.planning_entity pe
join ip.structure_map sm on pe.planning_code=sm.structure_code
join ip.structure_level sl on sm.depth=sl.level_num
left join ip.multi_attribute ma on pe.planning_code=ma.prime_structure_code and ma.alt_structure_name='Wbs32'
where pe.planning_code=@planning_code;

How can I make that recursive knowing I can get the hierarchical level of a task and I have a function that retrieves its father whatever level is requested.

Thanks for your help & enjoy your day