Based on this thread I don't think that I can do what I want/need but I need confirmation.

I have the following query:

SELECT COUNT(o1.team_id) AS orglevel, o1.team_id, o1.org_unit_name, z.budget, z.forecast, z.actual
FROM fab_organization o1 
CROSS JOIN fab_organization o2 
LEFT OUTER JOIN (
        SELECT o2.team_id, sum(bfa.budget_dollars) budget, sum(bfa.forecast_dollars) forecast, sum(bfa.actual_dollars) actual
        FROM fab_organization o1
        CROSS JOIN fab_organization o2
        INNER JOIN fab_bfa_summary bfa
        ON bfa.team_id = o1.team_id
        WHERE o1.lft BETWEEN o2.lft AND o2.rgt
        AND (
        (
            o1.fiscal_period = '2005-09-30'
                AND o2.fiscal_period = '2005-09-30'
                AND bfa.fiscal_period = '2005-09-30'
        )
        OR
        (
            o1.fiscal_period = '2005-10-31'
                AND o2.fiscal_period = '2005-10-31'
                AND bfa.fiscal_period = '2005-10-31'
        )
        -- Repeat for each month in fiscal period
    )
        GROUP BY o2.team_id
) z ON o1.team_id = z.team_id
WHERE o1.lft BETWEEN o2.lft AND o2.rgt
GROUP BY o1.team_id, o1.org_unit_name, o1.lft, z.budget, z.forecast, z.actual
ORDER BY o1.lft ASC

I have the inner/subquery working correctly via a dynamic list (thanks to Frans' assistance on this thread).

Now I am trying to use the dynamic list in for the OUTER LEFT JOIN but not quite able to get it wired up correctly. Do I need to look at alternatives?

Thanks!

Still stuck. The reason why I thought I could do it was this thread, but upon further examination, there are some obvious differences between the two situations. On a positive note, I have refactored the inner/sub query so that the whole query now looks like the following and I don't have to have logic to add in all the sub-conditions for each fiscal period. And an added bonus is that it is more efficient!

SELECT COUNT(*) AS orglevel, o1.team_id, o1.org_unit_name, z.budget, z.forecast, z.actual
FROM fab_organization o1 
CROSS JOIN fab_organization o2 
LEFT OUTER JOIN (
    SELECT o2.team_id, sum(bfa.budget_dollars) budget, sum(bfa.forecast_dollars) forecast, sum(bfa.actual_dollars) actual
    FROM fab_organization o1, fab_organization o2, fab_bfa_summary bfa
    WHERE o1.lft BETWEEN o2.lft AND o2.rgt
    AND o1.fiscal_period IN ('2005-09-30', '2005-10-31')
    AND o2.fiscal_period = o1.fiscal_period
    AND bfa.team_id = o1.team_id
    AND bfa.fiscal_period = o1.fiscal_period
    GROUP BY o2.team_id
) z ON o1.team_id = z.team_id
WHERE o1.lft BETWEEN o2.lft AND o2.rgt
GROUP BY o1.team_id, o1.org_unit_name, o1.lft, z.budget, z.forecast, z.actual
ORDER BY o1.lft ASC

I'm pretty sure I was running into a dead-end. I gave up and just implemented it as a stored procedure. As easy as that route is, not sure why I didn't do that many hours ago.