oracle field combination-CodePudding

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.

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

CodePudding user response：

You can use the DISTINCT keyword with the COUNT function to get the desired result -

with temp as (select gral.contrato, 
                     gral.campo,
                     count(CAMPO) OVER (partition by gral.contrato, campo) as counter
                     count(DISTINCT CAMPO) OVER (partition by gral.contrato, campo) as dist_counter 
                from CAM_TBL_ALERTA_GRAL gral
               WHERE FECHA_MODIFICACION >'01/07/2021'
             )
SELECT contrato,campo
  FROM temp
 WHERE counter >= 2
   AND dist_counter = 1