Clarity

 View Only
  • 1.  NSQL Error NPT-112 No Key

    Posted Jan 25, 2018 02:03 PM

    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.



  • 2.  Re: NSQL Error NPT-112 No Key
    Best Answer

    Posted Jan 25, 2018 02:27 PM

    The code you added needs to read like below; (I've highlighted the changed bit)

     

    --Added

    @select:dim_prop:user_def:implied:statusreport:resources.BusinessChampion:BusinessChampion@,

    @select:dim_prop:user_def:implied:statusreport:resources.ITLead:TSPManager@,

    @select:dim_prop:user_def:implied:statusreport:resources.ProjectManager:ProjectManager@,

    @select:dim_prop:user_def:implied:statusreport:resources.ManagedBy:ManagedBy@,

    --End Add



  • 3.  Re: NSQL Error NPT-112 No Key

    Posted Jan 25, 2018 03:10 PM

    David,

     

    Thank you. That was it!

     

    I need to go back and review my NSQL Training to better understand, but what I think I see as my error was that I "created" a new dimension (albeit accidently).  And you in your correction pointed out that I need to reference the dimension statusreport.



  • 4.  Re: NSQL Error NPT-112 No Key

    Posted Jan 25, 2018 03:14 PM

    Yes, exactly that! ^