Good Day Community,
I have the opportunity to implement my first NSQL in support of a new portlet. I did not write this from scratch, rather we pulled the NSQL from an existing portlet to update and add some additional fields to the portlet. What I am experiencing is that when I test the NSQL I receive the error NPT-112 The dimension resources does not have a dimension key defined.
My code is below, for the purposes of this question I have added --comment line to help identify the coded I have added to this NSQL.
SELECT @select:dim:user_def:implied:statusreport:sr.odf_pk:status_report_int_id@,mtogn
@select:dim_prop:user_def:implied:statusreport:sr.odf_parent_id:project_int_id@,
@select:dim_prop:user_def:implied:statusreport:i.code:project_id@,
@select:dim_prop:user_def:implied:statusreport:i.name:project_name@,
@select:dim_prop:user_def:implied:statusreport:@UPPER@(i.odf_object_code):investment_type_upper@,
@select:dim_prop:user_def:implied:statusreport:i.manager_id:project_manager_int_id@,
@select:dim_prop:user_def:implied:statusreport:r.full_name:project_manager@,
@select:dim_prop:user_def:implied:statusreport:pc.obj_request_type:project_type_id@,
@select:dim_prop:user_def:implied:statusreport:pt.name:project_type@,
@select:dim_prop:user_def:implied:statusreport:c.name:project_category@,
@select:dim_prop:user_def:implied:statusreport:CASE WHEN i.status IN (1,5,8) THEN 1 ELSE 0 END:status_id@,
@select:dim_prop:user_def:implied:statusreport:stat.name:status@,
@select:dim_prop:user_def:implied:statusreport:i.progress:progress_id@,
@select:dim_prop:user_def:implied:statusreport:pg.name:progress@,
@select:dim_prop:user_def:implied:statusreport:ic.obj_work_status:work_status_id@,
@select:dim_prop:user_def:implied:statusreport:ws.name:work_status@,
@select:dim_prop:user_def:implied:statusreport:pc.obj_status_reporting:status_reporting_id@,
@select:dim_prop:user_def:implied:statusreport:srf.name:status_reporting@,
@select:dim_prop:user_def:implied:statusreport:s.name:stage@,
@select:dim_prop:user_def:implied:statusreport:i.schedule_start:start_date@,
@select:dim_prop:user_def:implied:statusreport:@DBUSER@.COP_CALC_FINISH_FCT(i.schedule_finish):finish_date@,
@select:dim_prop:user_def:implied:statusreport:sr.name:report_name@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_report_date:report_date@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_report_status:report_status_id@,
@select:dim_prop:user_def:implied:statusreport:rs.name:report_status@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_report_update:status_report_update@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_key_accomplish:key_accomplishments@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_upcoming_act:upcoming_activities@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_phase:current_phase_int_id@,
@select:dim_prop:user_def:implied:statusreport:t.prName:current_phase@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_sched_milestone:next_milestone_on_track@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_schedule_exp:variance_explanation@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_scope_change:scope_change_req@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_scope_deliver:deliver_scope_changed@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_scope_obj:project_obj_changed@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_scope_exp:scope_explanation@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_cost_effort_rev:review_appr_problems@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_cost_eft_staff:staffing_avail_issues@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_cost_effort_ext:project_eff_ext_factors@,
@select:dim_prop:user_def:implied:statusreport:sr.cop_effort_exp:effort_explanation@,
@select:dim_prop:user_def:implied:statusreport:CASE WHEN lsr.report_order = 1 THEN 1 ELSE 0 END:latest_status_report@,
--Added
@select:dim_prop:user_def:implied:resources:resources.BusinessChampion:BusinessChampion@,
@select:dim_prop:user_def:implied:resources:resources.ITLead:TSPManager@,
@select:dim_prop:user_def:implied:resources:resources.ProjectManager:ProjectManager@,
@select:dim_prop:user_def:implied:resources:resources.ManagedBy:ManagedBy@,
--End Add
@select:metric:user_def:implied:sr.cop_overall_status:overall_status@,
@select:metric:user_def:implied:sr.cop_schedule_status:schedule_status@,
@select:metric:user_def:implied:sr.cop_scope_status:scope_status@,
@select:metric:user_def:implied:sr.cop_cost_eft_status:cost_effort_status@
FROM inv_investments i
INNER JOIN odf_ca_inv ic ON i.id = ic.id
INNER JOIN odf_ca_project pc ON i.id = pc.id
INNER JOIN inv_projects p ON i.id = p.prID
INNER JOIN odf_cop_prj_statusrpt_v2 sr ON i.id = sr.odf_parent_id
LEFT OUTER JOIN cop_prj_statusrpt_latest_v lsr ON sr.odf_pk = lsr.report_id
LEFT OUTER JOIN srm_resources r ON i.manager_id = r.user_id
LEFT OUTER JOIN cmn_lookups_v pt ON pc.obj_request_type = pt.lookup_code
AND pt.lookup_type = 'OBJ_IDEA_PROJECT_TYPE'
AND pt.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v c ON pc.obj_request_category = c.lookup_code
AND c.lookup_type = 'OBJ_IDEA_PROJECT_CATEGORY'
AND c.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v stat ON i.status = stat.lookup_enum
AND stat.lookup_type = 'INVESTMENT_OBJ_STATUS'
AND stat.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v pg ON i.progress = pg.lookup_enum
AND pg.lookup_type = 'INVESTMENT_OBJ_PROGRESS'
AND pg.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v ws ON ic.obj_work_status = ws.lookup_code
AND ws.lookup_type = 'OBJ_INVESTMENT_WORK_STATUS'
AND ws.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v srf ON pc.obj_status_reporting = srf.lookup_code
AND srf.lookup_type = 'OBJ_PROJECT_STATUS_REPORTING'
AND srf.language_code = @WHERE:PARAM:LANGUAGE@
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 = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN cmn_lookups_v rs ON sr.cop_report_status = rs.lookup_code
AND rs.lookup_type = 'OBJ_STATUSREPORT_REPORT_STATUS'
AND rs.language_code = @WHERE:PARAM:LANGUAGE@
--added this join
LEFT OUTER JOIN (
SELECT
inv.code
, res.full_name AS BusinessChampion
, res2.full_name AS ProjectManager
, res3.full_name AS ExecutiveSponsor
, res4.full_name AS ITLead
, UPPER(mgtby.department_owner) AS ManagedBy
FROM
--Business Champion
inv_investments inv LEFT JOIN odf_ca_inv bc
ON inv.ID = bc.id
LEFT JOIN srm_resources res
ON bc. ph_sponsor2 = res.id
--Project Manager
LEFT JOIN srm_resources res2
ON inv.manager_id = res2.user_id
--Executive Sponsor
LEFT JOIN ODF_CA_INV es
ON inv.id = es.id
LEFT JOIN srm_resources res3
ON es.ph_sponsor1 = res3.id
--IT Lead
LEFT JOIN odf_ca_project PSTL
ON inv.id = pstl.id
LEFT JOIN SRM_RESOURCES res4
on pstl.portfoliomanager_id = res4.user_id
--porfolio category
left join odf_ca_inv portcat
ON inv.id = portcat.id
--managed by
LEFT JOIN niku.odf_ca_project MgtBy
ON inv.id = mgtby.id
WHERE
INV.CODE LIKE 'Prj-%'
) Resources
ON Resources.Code = i.code
--end added join
WHERE i.is_active = 1
AND p.is_template = 0
AND (p.is_program = 0 OR @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:program@,0) = 1)
AND (@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0) = 0 OR
i.id IN (SELECT obsa.record_id
FROM prj_obs_associations obsa, prj_obs_units_flat obsf
WHERE obsa.unit_id = obsf.unit_id
AND obsa.table_name = 'SRM_PROJECTS'
AND obsf.branch_unit_id = @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0)))
AND @WHERE:SECURITY:PROJECT:i.id@
AND @FILTER@
I would appreciate guidance on how where I may have gone wrong, and how we can implement this query.
Thank you.