'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