Clarity PPM

Expand all | Collapse all

One investment will be tagged to different portfolio?

  • 1.  One investment will be tagged to different portfolio?

    Posted 08-01-2018 04:13 PM

    I need to find PORTFOLIO ID and NAME for Investment, i'm trying to map INV_INVESTMENT to PFM_INVESTMENTS to get the details from PFM_PORTFOLIOS. i'm getting "too many rows" error due to more than one portfolio for one investment

     

    can you please suggest me a way to solve it. 

     

    (select distinct pma.name,pma.id
    from
    PFM_INVESTMENTS pmac
    inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
    inner join inv_investments inv on pmac.investment_id=inv.id
    and inv.id=x.investment_id)

     



  • 2.  Re: One investment will be tagged to different portfolio?

    Posted 08-01-2018 07:25 PM

    From my understanding, you query looks ok to fetch portfolio name and id, however it seems to be like a sub-query to other main query. Need to analyze your full query to know the cause of the error.



  • 3.  Re: One investment will be tagged to different portfolio?

    Posted 08-02-2018 05:20 AM

    Please find the full query.

     

    SELECT
    --x.portfolio_id,x.portfolio_name,
    (select distinct pma.name
    from
    PFM_INVESTMENTS pmac
    inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
    inner join inv_investments inv on pmac.investment_id=inv.id
    and inv.ODF_OBJECT_CODE='project' and inv.id=x.investment_id) a,
    --(select distinct id from PFM_INVESTMENTS a where is_active=1 and a.investment_id=x.investment_id and rownum <2) a,
    x.investment_id,
    x.GIL_ID,
    x.investment_name,
    x.Project_portfolio,
    nvl((select distinct res.first_name||' '||res.last_name from srm_resources res, cmn_sec_users usr where res.USER_ID = usr.id and res.id = X.It_lead),'N/A') IT_Lead,
    x.investment_manager,
    x.start_date,
    x.finish_date,
    x.stage_code,
    decode(x.c_overall_status,'30','Red','20','Yellow','10','Green','White') Stage,
    x.gil_corr_action as gil_corr_action,
    x.gil_lead_action as gil_lead_action,
    x.cop_upcoming_act as cop_upcoming_act

    FROM (SELECT i.id investment_id,
    pp.name as Project_portfolio
    ,ic.gil_target_in_svc_go as gil_in_serv
    ,c.name as project_type
    ,cat.name as project_category,
    ic.gil_unique_id GIL_ID,
    i.name investment_name,
    m.full_name investment_manager,
    i.schedule_start start_date,

    ic.gil_prj_finish_date finish_date,

    sr.cop_report_date report_date,

    s.name stage_code,
    NVL(sr.cop_overall_status,0) as c_overall_status,
    NVL(sr.cop_schedule_status,0) c_schedule_status,
    NVL(sr.cop_scope_status,0) c_scope_status,
    NVL(sr.cop_cost_eft_status,0) c_cost_effort_status,

    nvl(sr2.cop_overall_status,0) as p_overall_status,
    NVL(sr2.cop_schedule_status,0) p_schedule_status,
    NVL(sr2.cop_scope_status,0) p_scope_status,
    NVL(sr2.cop_cost_eft_status,0) p_cost_effort_status,
    sr.gil_var_detail,
    sr.cop_key_accomplish,
    sr.cop_upcoming_act,

    sr.cop_report_update ,
    sr.gil_corr_action,
    sr.gil_lead_action,
    ic.gil_multi_phase_rel multi_phase,
    ic.gil_it_lead IT_LEAD
    --,ppma.port_id portfolio_id,ppma.port_name portfolio_name

    FROM inv_investments i
    INNER JOIN odf_ca_inv ic ON i.id = ic.id
    left join cmn_lookups_v pp on (pp.lookup_type = 'GIL_PRJ_PORT' and pp.language_code= 'en' and pp.lookup_code = ic.gil_prj_port)

    left outer join inv_projects p ON i.id = p.prID
    left outer JOIN odf_ca_project pc ON i.id = pc.id

    left join cmn_lookups_v c on (c.lookup_type = 'OBJ_IDEA_PROJECT_TYPE' and c.language_code= 'en' and c.lookup_code = pc.obj_request_type)
    left join cmn_lookups_v cat on (cat.lookup_type = 'OBJ_IDEA_PROJECT_CATEGORY' and cat.language_code= 'en' and cat.lookup_code = pc.obj_request_category)
    left join cmn_lookups_v prg on (prg.lookup_type = 'INVESTMENT_OBJ_PROGRESS' and prg.language_code= 'en' and prg.lookup_enum = i.progress)


    LEFT OUTER JOIN cop_prj_statusrpt_latest_v r ON i.id = r.investment_id
    AND r.report_order = 1
    LEFT OUTER JOIN odf_cop_prj_statusrpt_v2 sr ON r.report_id = sr.odf_pk

    LEFT OUTER JOIN srm_resources m ON i.manager_id = m.user_id

    LEFT OUTER JOIN cop_prj_statusrpt_latest_v r2 ON i.id = r2.investment_id AND
    r2.report_order = 2
    LEFT OUTER JOIN odf_cop_prj_statusrpt_v2 sr2 ON r2.report_id = sr2.odf_pk

    LEFT OUTER JOIN cmn_lookups_v s ON i.stage_code = s.lookup_code
    AND s.lookup_type = 'INV_STAGE_TYPE'
    AND s.language_code = 'en'

    -- LEFT OUTER JOIN PFM_INVESTMENTS ppma on i.id=ppma.investment_id
    /*
    INNER JOIN (select distinct pma.id port_id,pma.name port_name, inv.id inv_id
    from
    PFM_INVESTMENTS pmac
    inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
    inner join inv_investments inv on pmac.investment_id=inv.id) ppma on i.id=ppma.inv_id
    */
    WHERE (NVL(p.is_template,0) = 0)
    AND (NVL(p.is_program,0) = 0)
    and i.id in (select pmac.investment_id
    from
    PFM_INVESTMENTS pmac
    inner join PFM_PORTFOLIOS pma on pmac.portfolio_id = pma.id
    inner join inv_investments inv on pmac.investment_id=inv.id
    -- and inv.ODF_OBJECT_CODE='project'
    and pma.id = '5034011'
    )

    group by
    i.id,
    pp.name
    ,ic.gil_target_in_svc_go
    ,c.name
    ,cat.name ,
    ic.gil_unique_id,
    i.name ,
    m.full_name ,
    i.schedule_start ,

    ic.gil_prj_finish_date ,

    sr.cop_report_date ,

    s.name ,
    NVL(sr.cop_overall_status,0),
    NVL(sr.cop_schedule_status,0) ,
    NVL(sr.cop_scope_status,0) ,
    NVL(sr.cop_cost_eft_status,0) ,
    nvl(sr2.cop_overall_status,0) ,
    NVL(sr2.cop_schedule_status,0) ,
    NVL(sr2.cop_scope_status,0) ,
    NVL(sr2.cop_cost_eft_status,0) ,

    sr.gil_var_detail,
    sr.cop_key_accomplish,
    sr.cop_upcoming_act,
    sr.cop_report_update,
    sr.gil_corr_action,
    sr.gil_lead_action,
    ic.gil_multi_phase_rel,
    ic.gil_it_lead
    --,ppma.port_id,ppma.port_name
    --,ppma.portfolio_id
    ) x
    Where 1=1
    --and x.gil_id='GIL1952'

    ORDER BY
    x.investment_name ASC