AnsweredAssumed Answered

NSQL Error NPT-112 No Key

Question asked by mtognetti on Jan 25, 2018
Latest reply on Jan 25, 2018 by mtognetti

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.

Outcomes