AnsweredAssumed Answered

Error: NPT-217 issue

Question asked by RnFernandez on Aug 3, 2018
Latest reply on Aug 5, 2018 by roland.parrotte

Recently we are having the following error in a query of "Monthly Incurred Hours" that was not modified.

Could you guide us in solving this problem?

 

ErrorNPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete.

 

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:HORAS:L1.name||INV.id||ASG.prtaskid||PRM_ROLE.unique_name||ASG.prresourceid||to_char(SLC.slice_date,'mm')||to_char(SLC.slice_date,'yyyy'):id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ROW_NUMBER() ASG.prtaskid:task_int_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TSK.prexternalid:task_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TSK.prname:task_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG.prresourceid:resource_int_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:dep.id:gerencia_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:dep.DEPARTCODE:gerencia_n@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:UNT_P.id:gerencia_obs_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:UNT_P.name:gerencia_obs_n@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RES.unique_name:resource_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RES.full_name:resource_full_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when RES.is_external=0 then 'No' else 'Si' end:contratado@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.id:inv_int_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.code:inv_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.name:inv_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:par_2.name:Proyecto_progr@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:par_2.padre_code:progr_cod@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when par.name is null then inv.name else par.name end:Proyecto_Padre@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when par.padre_code is null then inv.code else par.padre_code end:padre_cod@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:l5.name:Objetivo:objetivo@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L6.name:prioridad_n:prioridad_cliente@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_prioridad_p_clnt:prioridad_clnt_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when INV.odf_object_code = 'project' or INV.odf_object_code = 'idea' then L1.name else case when INV.odf_object_code = 'application' then 'Mantenimiento' else l4.name end end:inv_tipo_proyecto@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV.odf_object_code:inv_tipo_inversion@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:inv3.sb_tipo_proyecto:tipo_proyecto_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_area_req:area_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_area_req2:subarea_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_hs_indiirectas:hs_indirectas@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV3.sb_prioridad_p_clnt:prioridad_neg_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L6.name:prioridad_neg_name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:inv4.sb_req_procesos:req_ing_procesos@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when inv4.sb_req_procesos = 1 then 'S' else 'N' end:req_ing_procesos_x@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L2.name:inv_area@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L3.name:inv_subarea@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L4.name:inv_tipo_otro_trabajo@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV5.sb_tipo_otro_trabajo:inv_tipo_otro_trabajo_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.unique_name:Grupo_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.full_name:Grupo_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:PRM_ROLE.id:Grupo_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV_ROLE.unique_name:invrole_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:INV_ROLE.full_name:grupo_proyecto_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG_ROLE.unique_name:assignmentrole_code@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:ASG_ROLE.full_name:grupo_tarea_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:COMP.company_id:company_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:COMP.company_name:company_name@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:to_number(to_char(SLC.slice_date,'mm')):mes@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:to_number(to_char(SLC.slice_date,'yyyy')):anio@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TIM.full_name:manager_n@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:TIM.user_id:manager_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:RM.full_name:rm_n@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:rm.id:rm_id@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:L10.name:clasif_recur@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:recu_odf.sb_clasif_rec:clasif_recur_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:recu_odf.sb_tipo_recurso:tipo_recurso_contratado@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:case when recu_odf.sb_tipo_recurso IS NULL THEN 'Efectivo' else LR4.name end:tipo_recurso_contratado_n@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:HORAS:sum(SLC.slice):slice@


FROM     PRJ_BLB_SLICES SLC

         left outer join PRJ_BLB_SLICEREQUESTS REQ on SLC.slice_request_id = REQ.id
         left outer join PRASSIGNMENT ASG          on SLC.prj_object_id = ASG.prid
         left outer join PRTASK TSK                on ASG.prtaskid = TSK.prid
         left outer join INV_INVESTMENTS INV       on TSK.prprojectid = INV.id
         left outer join ODF_CA_INV INV3           on INV.id = INV3.id
         left outer join ODF_CA_OTHER INV5         on INV.id = INV5.id
         left outer join PRTEAM T                  on ASG.team_id = T.prid
         left outer join SRM_RESOURCES RES         on ASG.prresourceid = RES.id
         left outer join PRJ_RESOURCES RES_G       on RES.id = RES_G.prid
         left outer join ODF_CA_RESOURCE recu_odf  on RES.id = recu_odf.id
         left outer join SRM_RESOURCES PRM_ROLE    on RES_G.prprimaryroleid = PRM_ROLE.id

         /* proveedor */
         LEFT OUTER JOIN ODF_CA_RESOURCE COMP_RES  ON RES.ID = COMP_RES.ID
         left outer join SRM_COMPANIES COMP        on COMP.company_id = COMP_RES.SB_COMPANIA

         left outer join ODF_CA_RESOURCE res_odf   on prm_role.id = res_odf.id
         left outer join DEPARTMENTS dep           on res_odf.sb_gerencia_agrup = dep.id
         left outer join SRM_RESOURCES INV_ROLE    on T.prroleid = INV_ROLE.id
         left outer join SRM_RESOURCES ASG_ROLE    on ASG.role_id = ASG_ROLE.id

      /* Tablas Gerencia y Gerente NUEVA */
       left outer join
         (select ASSOC.record_id,
                 ASSOC.unit_id
         from PRJ_OBS_ASSOCIATIONS ASSOC,
              PRJ_OBS_UNITS UNT,
              PRJ_OBS_TYPES TYP
         where UNT.type_id = TYP.id
           and ASSOC.unit_id = UNT.id
           and ASSOC.table_name = 'SRM_RESOURCES'
           and TYP.unique_name ='sb_dept') RES_ASSOC
       on RES.id = RES_ASSOC.record_id
       left outer join PRJ_OBS_UNITS UNIT         on RES_ASSOC.unit_id = UNIT.id
       left outer join
          (select FLAT.unit_id,
                FLAT.branch_unit_id,
                DEP.id
        FROM
             PRJ_OBS_UNITS_FLAT FLAT,
             PRJ_OBS_UNITS OBS,
             DEPARTMENTS DEP,
             ODF_CA_DEPARTMENT DEP2
          where FLAT.branch_unit_id = OBS.id
            and OBS.id = DEP.obs_unit_id
            and DEP.id = DEP2.id
            and DEP2.sb_es_gerencia = 1 ) FLT
          on RES_ASSOC.unit_id = FLT.unit_id
          left outer join PRJ_OBS_UNITS UNT_P    on FLT.branch_unit_id = UNT_P.id
        
       /* Tabla con etiquetas de Tipo Proyecto */
          left outer join CMN_LOOKUPS_V L1
          on (INV3.sb_tipo_proyecto = L1.lookup_code
              and L1.lookup_type = 'SB_TIPO_PROYECTO'
              and L1.language_code = 'en')

       /* Tabla con etiquetas de Area de Requerimiento */
          left outer join CMN_LOOKUPS_V L2
          on (INV3.sb_area_req = L2.lookup_code
              and L2.lookup_type = 'SB_AREAS_REQ'
              and L2.language_code = 'en')


       /* Tabla con etiquetas de Subarea de Requerimiento */
          left outer join CMN_LOOKUPS_V L3
          on (INV3.sb_area_req2 = L3.lookup_code
              and L3.lookup_type = 'SB_AREAS_REQ2'
              and L3.language_code = 'en')


       /* Tabla con etiquetas de Tipo Otro Trabajo*/
          left outer join CMN_LOOKUPS_V L4
          on (INV5.sb_tipo_otro_trabajo = L4.lookup_code
              and L4.lookup_type = 'SB_TIPO_OTRO_TRABAJO'
              and L4.language_code = 'en')

       /* objetivo*/
         LEFT OUTER JOIN cmn_lookups_v L5
         on (L5.language_code = 'en' and  INV.GOAL_CODE=
             L5.lookup_code and l5.lookup_type = 'INVESTMENT_GOAL_TYPE')


      /*Tabla con de prioridades por cliente*/
        left outer join CMN_LOOKUPS_V L6
        on (INV3.sb_prioridad_p_clnt = L6.lookup_code
        and L6.lookup_type = 'SB_PRIORIDAD_POR_CLIENTE'
        and L6.language_code = 'en')


      /* padres  */
         left outer join
              (select id, child_id, name, padre_code from
              (select inv.id, HIE.child_id, inv.name, inv.code padre_code from
              INV_HIERARCHIES HIE left outer join INV_INVESTMENTS INV
              on HIE.parent_id = INV.id left outer join INV_PROJECTS PRJ
              on HIE.parent_id = PRJ.prid  where HIE.parent_id IS NOT NULL
              and INV.odf_object_code = 'project' and prj.is_program = 0) parent ) par
          on inv.id = par.child_id

      /* Tabla programas */
       left outer join
            (select id, child_id, name, padre_code from
            (select inv.id, HIE.child_id, inv.name, inv.code padre_code from
             INV_HIERARCHIES HIE left outer join INV_INVESTMENTS INV
             on HIE.parent_id = INV.id left outer join INV_PROJECTS PRJ
             on HIE.parent_id = PRJ.prid  where HIE.parent_id IS NOT NULL
             and INV.odf_object_code = 'project' and prj.is_program=1) parent ) par_2
         on inv.id = par_2.child_id


        /* Tabla con Manager, Gestor, Responsable o TIM */
               left outer join SRM_RESOURCES TIM
                on INV.manager_id = TIM.user_id

        /* Tabla con RM Asignado */
          left outer join ODF_CA_PROJECT INV4
           on INV.id = INV4.id

          left outer join SRM_RESOURCES RM
          on INV3.sb_rm = RM.id
   
       /* Tabla con etiquetas de Prioridad de Unidad de Negocio*/
         left outer join CMN_LOOKUPS_V L9
          on (INV3.obj_align_factor2 = l9.lookup_code
          and l9.lookup_type = 'OBJ_INVESTMENT_ALIGNMENT_ENUM'
          and l9.language_code = 'en')

       /* Tabla con etiquetas de Clasificacion Recursos*/
          LEFT OUTER JOIN cmn_lookups_v L10 ON            (L10.language_code = @WHERE:PARAM:LANGUAGE@
            and recu_odf.sb_clasif_rec = L10.lookup_enum    and L10.lookup_type = 'SB_CLASIF_REC')

       left outer join CMN_LOOKUPS_V LR4              on     (LR4.lookup_code = recu_odf.sb_tipo_recurso
       and LR4.lookup_type = 'SB_TIPOS_RECURSO_LISTA'        and LR4.language_code = @WHERE:PARAM:LANGUAGE@)

WHERE REQ.request_name = 'Datos Reales - Mensual'
  AND
  ( res_odf.sb_funcion_es_factur = @WHERE:PARAM:USER_DEF:INTEGER:ES_FACTUR@
    OR @WHERE:PARAM:USER_DEF:INTEGER:ES_FACTUR@ IS NULL )

  AND ( INV.code != @WHERE:PARAM:USER_DEF:STRING:ignorar_inv_code@ or @WHERE:PARAM:USER_DEF:STRING:ignorar_inv_code@ is null )
  AND @FILTER@


GROUP BY L1.name||INV.id||ASG.prtaskid||PRM_ROLE.unique_name||ASG.prresourceid||to_char(SLC.slice_date,'mm')||to_char(SLC.slice_date,'yyyy'),
         ASG.prtaskid,
         TSK.prexternalid,
         TSK.prname,
         ASG.prresourceid,
         RES.unique_name,
         RES.full_name,
         case when RES.is_external=0 then 'No' else 'Si' end,
         INV.id,
         INV.code,
         INV.name,
         par_2.name,
         par_2.padre_code,
         case when par.name is null then inv.name else par.name end,
         case when par.padre_code is null then inv.code else par.padre_code end,
         l5.name,
         l6.name,
         INV3.sb_prioridad_p_clnt,
        case when INV.odf_object_code = 'project' or INV.odf_object_code = 'idea' then L1.name else case when INV.odf_object_code = 'application' then 'Mantenimiento' else l4.name end end,
         INV.odf_object_code,
         inv3.sb_tipo_proyecto,
         INV3.sb_area_req,
         INV3.sb_area_req2,
         INV3.sb_hs_indiirectas,
         INV3.sb_prioridad_p_clnt,
         L6.name,
         inv4.sb_req_procesos,
         case when inv4.sb_req_procesos = 1 then 'S' else 'N' end,
         L2.name ,
         L3.name ,
         L4.name ,
         INV5.sb_tipo_otro_trabajo,
         PRM_ROLE.unique_name,
         PRM_ROLE.full_name,
         INV_ROLE.unique_name,
         INV_ROLE.full_name,
         ASG_ROLE.unique_name,
         ASG_ROLE.full_name,
         COMP.company_id,
         COMP.company_name,
         to_number(to_char(SLC.slice_date,'mm')),
         to_number(to_char(SLC.slice_date,'yyyy')),
         TIM.full_name,
         TIM.user_id,
         RM.full_name,
         rm.id,
         L10.name,
         recu_odf.sb_clasif_rec,
         PRM_ROLE.id,
         dep.id,
         dep.DEPARTCODE,
         UNT_P.id,
         recu_odf.sb_tipo_recurso,
         case when recu_odf.sb_tipo_recurso IS NULL THEN 'Efectivo' else LR4.name end,
         UNT_P.name
ORDER BY INV.code,
         TSK.prname,
         PRM_ROLE.full_name,
         RES.unique_name,
         to_number(to_char(SLC.slice_date,'mm')),
         to_number(to_char(SLC.slice_date,'yyyy')),
         RES.unique_name

Outcomes