AnsweredAssumed Answered

Aggregate values from sub-projects

Question asked by BradGibson on Mar 31, 2011
Latest reply on Apr 12, 2011 by BradGibson
Here's a challenge:

I'm writing a query based portlet for programs...

The program contains x projects (which are really just subprojects of the program) and each of the sub-projects may contain one or more subprojects.

At the project level, I use this query to get a sum of values from my subprojects:

using a number field called os_actual_capital that is in the ODF_CA_PROJECTS table.

(nvl((Select sum(nvl(socp.os_actual_capital,0)) From inv_investments si, inv_projects sp, prsubproject ss, prtask st, inv_investments sj,odf_ca_project socp
Where ss.prtaskid = st.prid
And st.prprojectid = si.id
and sp.is_program = 0
And ss.prrefprojectid = sj.id
And sp.prid = si.id
and socp.id=sj.id
and socp.ove_initiative_stat not in ('Cancelled')
and socp.ove_cr<>1
and si.id = i.id),0)+ocp.os_actual_capital) os_actual_capital,


Which works fine... but now I'm going to have 3 levels...

Program
Project
Subproject

How can I re-write the query to sum the values for each level?
I believe the current query will miss the sub-projects (3rd level)

Any thoughts?

Outcomes