'Does not recognize ROW_NUMBER() function OVER (PARTITION BY
Good afternoon
I am using the ROW_NUMBER() function OVER (PARTITION BY, to bring only unique values of a concatenation of fields, however, when running the query, partition by works because it tells me the number of times the field that I indicate is repeated, but wanting to only bring a record from the list that appears it does not take it, so put only those with a value of 1 in my condition, it continues to bring me the entire data set.
SELECT DISTINCT
C1_ID_ALERTA,
C2_NOMBRE_USUARIO,
C3_BP,
C32_AREA_BANCARIA,
C4_CONTRATO,
C41_CENTRO_FINANCIERO,
C5_CAMPO,
C6_VALOR_ANTERIOR,
C7_VALOR_ACTUAL,
C8_FECHA_MODIFICACION,
C9_HORA_MODIFICACION,
C10_TIPO_LOG,
enum
FROM (
SELECT T1.*,ROW_NUMBER() OVER (PARTITION BY T1.C1_ID_ALERTA ORDER BY T1.C1_ID_ALERTA DESC)as ENUM
FROM (
SELECT
area_bancaria||contrato||valor_anterior||valor_actual C1_ID_ALERTA,
nombre_usuario C2_NOMBRE_USUARIO,
bp C3_BP,
area_bancaria C32_AREA_BANCARIA,
contrato C4_CONTRATO,
cenfinancierodes C41_CENTRO_FINANCIERO,
campo C5_CAMPO,
valor_anterior C6_VALOR_ANTERIOR,
valor_actual C7_VALOR_ACTUAL,
fecha_modificacion C8_FECHA_MODIFICACION,
hora_modificacion C9_HORA_MODIFICACION,
tipo_log C10_TIPO_LOG
FROM (SELECT *
FROM CAM_TBL_ALERTA_CONCENTRADO conc
INNER JOIN IBM_I2.I2_CONTRATOS cont
ON conc.contrato = LTRIM(cont.nocont,'0')
AND cont.institucion = conc.area_bancaria
WHERE
TIPO_LOG='LOG BP'
AND OPERACION='2'
)
)T1
) T2
WHERE ENUM=1
AND C1_ID_ALERTA IS NOT NULL
AND C10_TIPO_LOG='LOG BP'
AND C5_CAMPO NOT IN ('GIRO_CNBV','BIRTHDT','D_F_ESCRITURA','CLAVE_COL',
'CONSNUMBER','XSEXF','ADDRNUMBER','NAME_ORG1','NAME_ORG2','NAME_ORG3','TAXNUM',
'RELTYP','ADDR_TYPE','NAME_LAST','NAME_LST2','NAMEMIDDLE','NAME_FIRST',
'STREET','POST_CODE1','HOUSE_NUM1','LOCALIDAD','REGION','ROOMNUMBER',
'TOWN','CLAVE_COL','ADDR_TYPE','RELTYP')
OR c5_campo='SMTP_ADDR'
OR c5_campo='TEL_NUMBER'
It doesn't mark any error.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
