'oracle field combination

I am making a query to a table, where I am interested in bringing the contract numbers that are being repeated, and for this I use partition by, and so far everything is fine. However, I require that you only bring me those contract numbers that in the "CAMPO" field meet a combination of values, both SMTP_ADDR and TEL_NUMBER and these values ​​are not the same in the contract numbers, as shown in the image. Record 1 and 2 should not output, and 3 and 4 is how you would expect them to output.

enter image description here

my query is this:

with temp as
  (
  select 
     gral.contrato, 
     gral.campo,  
     count(*) over (partition by gral.contrato) as counter 
   from CAM_TBL_ALERTA_GRAL gral
       WHERE  
         FECHA_MODIFICACION >'01/07/2021'
                    AND 
                    (CAMPO='SMTP_ADDR')
                    OR CAMPO='TEL_NUMBER'
    )
     select contrato,campo,counter
    from temp
        where counter >= 2      



 


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source