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
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
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
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')
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')
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')
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')
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')
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')
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
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
left outer join SRM_RESOURCES TIM
on INV.manager_id = TIM.user_id
left outer join ODF_CA_PROJECT INV4
on INV.id = INV4.id
left outer join SRM_RESOURCES RM
on INV3.sb_rm = RM.id
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')
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