Recursive Table function

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 (
@PlanningRasp _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=@PlanningRasp _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

Answer 1

Hi @Pascale Pirson ,

Welcome to Microsoft Q&A!

Are you dealing with Recursive Table Function in SQL Server or other database management system?

If it is in SQL Server, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample after executing this function.

Per my limited knowledge, normally we use Recursive CTE to get the hierarchical levels or table-valued function to return a table as result.

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.