NPT-217: This query produced duplicate dimensional data
when I put ... AND (A.RAW_VALUE_AFTER='EnGarantia' OR A.RAW_VALUE_AFTER='cerrado') , the result "CUMPLEN ON CUMPLEN.MES=TODOS.MES" give me error
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:EMY:TODOS.MES:MES@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:EMY:ROUND(@NVL@(CUMPLEN.CUENTA/TODOS.CUENTA,0),4):RATIO@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:EMY:CASE WHEN @NVL@(CUMPLEN.CUENTA/TODOS.CUENTA,0) > TODOS.LIMITE THEN 1.0 ELSE 0.0 END:CUMPLIMIENTO@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:EMY:@NVL@(CUMPLEN.CUENTA,0):VECES_CUMPLIDO@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:EMY:TODOS.CUENTA:TOTAL@
FROM (SELECT DEF.CC_LIMITE LIMITE, TO_CHAR(AUD.CREATED_DATE, 'YYYY-MM') MES, COUNT(IND.ID) CUENTA
FROM ODF_CA_CC_INDICADOR_ANS IND
INNER JOIN ODF_CA_PROJECT PRJ ON PRJ.ID=IND.ODF_PARENT_ID
INNER JOIN INV_INVESTMENTS INV ON INV.ID=PRJ.ID
INNER JOIN (SELECT OA.RECORD_ID, OA.UNIT_ID
FROM PRJ_OBS_ASSOCIATIONS OA
INNER JOIN PRJ_OBS_UNITS OU ON OU.ID=OA.UNIT_ID AND OA.TABLE_NAME='SRM_PROJECTS' AND OU.UNIQUE_NAME IN ('factoria1')
INNER JOIN PRJ_OBS_TYPES OT ON OT.ID=OU.TYPE_ID AND OT.UNIQUE_NAME='factorias'
) FAC ON FAC.RECORD_ID = INV.ID
INNER JOIN (
SELECT A.OBJECT_ID, MAX(A.CREATED_DATE) CREATED_DATE, A.RAW_VALUE_AFTER
FROM CMN_AUDITS A
WHERE A.ATTRIBUTE_CODE='obj_work_status' AND A.OBJECT_CODE='project' AND (A.RAW_VALUE_AFTER='EnGarantia' OR A.RAW_VALUE_AFTER='cerrado')
GROUP BY A.OBJECT_ID, A.RAW_VALUE_AFTER
) AUD ON AUD.OBJECT_ID = INV.ID
INNER JOIN (SELECT T.PK_ID, MIN(T.VALUE) VALUE
FROM ODF_MULTI_VALUED_LOOKUPS T
WHERE T.ATTRIBUTE='cc_tamano'
AND T.OBJECT='project' AND (T.VALUE=@WHERE:PARAM:USER_DEF:STRING:P_TAMANO@ OR @WHERE:PARAM:USER_DEF:STRING:P_TAMANO@ IS NULL)
GROUP BY T.PK_ID) TAM ON TAM.PK_ID=PRJ.ID
INNER JOIN ODF_CA_CC_DEFINICION_ANS DEF ON DEF.NAME=IND.NAME
WHERE 1=1
AND (IND.NAME='EMY002' or IND.NAME='SP002')
AND (PRJ.IDEAPRIORITY=@WHERE:PARAM:USER_DEF:INTEGER:P_PRIORIDAD@ OR @WHERE:PARAM:USER_DEF:INTEGER:P_PRIORIDAD@ IS NULL)
AND (AUD.CREATED_DATE >= @WHERE:PARAM:USER_DEF:DATE:P_FECHA_DESDE@ OR @WHERE:PARAM:USER_DEF:DATE:P_FECHA_DESDE@ IS NULL)
AND (AUD.CREATED_DATE <= @WHERE:PARAM:USER_DEF:DATE:P_FECHA_HASTA@ OR @WHERE:PARAM:USER_DEF:DATE:P_FECHA_HASTA@ IS NULL)
AND AUD.CREATED_DATE <= DEF.CC_VALIDO_HASTA
AND AUD.CREATED_DATE >= DEF.CC_VALIDO_DESDE
AND DEF.ID IN (SELECT DISTINCT F.PK_ID
FROM ODF_MULTI_VALUED_LOOKUPS F
WHERE F.OBJECT='cc_definicion_ans' AND F.ATTRIBUTE='cc_factorias' AND FAC.UNIT_ID=F.VALUE)
GROUP BY DEF.CC_LIMITE, TO_CHAR(AUD.CREATED_DATE, 'YYYY-MM')) TODOS
LEFT JOIN
(SELECT DEF.CC_LIMITE LIMITE, TO_CHAR(AUD.CREATED_DATE, 'YYYY-MM') MES, COUNT(IND.ID) CUENTA
FROM ODF_CA_CC_INDICADOR_ANS IND
INNER JOIN ODF_CA_PROJECT PRJ ON PRJ.ID=IND.ODF_PARENT_ID
INNER JOIN INV_INVESTMENTS INV ON INV.ID=PRJ.ID
INNER JOIN (SELECT OA.RECORD_ID, OA.UNIT_ID
FROM PRJ_OBS_ASSOCIATIONS OA
INNER JOIN PRJ_OBS_UNITS OU ON OU.ID=OA.UNIT_ID AND OA.TABLE_NAME='SRM_PROJECTS' AND OU.UNIQUE_NAME IN ('factoria1')
INNER JOIN PRJ_OBS_TYPES OT ON OT.ID=OU.TYPE_ID AND OT.UNIQUE_NAME='factorias'
) FAC ON FAC.RECORD_ID = INV.ID
INNER JOIN (
SELECT A.OBJECT_ID, MAX(A.CREATED_DATE) CREATED_DATE, A.RAW_VALUE_AFTER
FROM CMN_AUDITS A
WHERE A.ATTRIBUTE_CODE='obj_work_status' AND A.OBJECT_CODE='project' AND (A.RAW_VALUE_AFTER='EnGarantia' OR A.RAW_VALUE_AFTER='cerrado')
GROUP BY A.OBJECT_ID, A.RAW_VALUE_AFTER
) AUD ON AUD.OBJECT_ID = INV.ID
INNER JOIN (SELECT T.PK_ID, MIN(T.VALUE) VALUE
FROM ODF_MULTI_VALUED_LOOKUPS T
WHERE T.ATTRIBUTE='cc_tamano'
AND T.OBJECT='project'
AND T.OBJECT='project' AND (T.VALUE=@WHERE:PARAM:USER_DEF:STRING:P_TAMANO@ OR @WHERE:PARAM:USER_DEF:STRING:P_TAMANO@ IS NULL)
GROUP BY T.PK_ID) TAM ON TAM.PK_ID=PRJ.ID
INNER JOIN ODF_CA_CC_DEFINICION_ANS DEF ON DEF.NAME=IND.NAME
WHERE 1=1
AND (IND.NAME='EMY002' or IND.NAME='SP002')
AND IND.CC_ESTADO_IND_ANS=1
AND (PRJ.IDEAPRIORITY=@WHERE:PARAM:USER_DEF:INTEGER:P_PRIORIDAD@ OR @WHERE:PARAM:USER_DEF:INTEGER:P_PRIORIDAD@ IS NULL)
AND (AUD.CREATED_DATE >= @WHERE:PARAM:USER_DEF:DATE:P_FECHA_DESDE@ OR @WHERE:PARAM:USER_DEF:DATE:P_FECHA_DESDE@ IS NULL)
AND (AUD.CREATED_DATE <= @WHERE:PARAM:USER_DEF:DATE:P_FECHA_HASTA@ OR @WHERE:PARAM:USER_DEF:DATE:P_FECHA_HASTA@ IS NULL)
AND AUD.CREATED_DATE <= DEF.CC_VALIDO_HASTA
AND AUD.CREATED_DATE >= DEF.CC_VALIDO_DESDE
AND DEF.ID IN (SELECT DISTINCT F.PK_ID
FROM ODF_MULTI_VALUED_LOOKUPS F
WHERE F.OBJECT='cc_definicion_ans' AND F.ATTRIBUTE='cc_factorias' AND FAC.UNIT_ID=F.VALUE)
GROUP BY DEF.CC_LIMITE, TO_CHAR(AUD.CREATED_DATE, 'YYYY-MM')) CUMPLEN ON CUMPLEN.MES=TODOS.MES
WHERE @FILTER@