SQL find units not having

Question

My Query :

SELECT ami.unit_type,ami.unit_nr, nwmp.metering_point_id,CR.register_name from nw_metering_connection@AMIAMI AMI
LEFT JOIN NW_METERING_POINT@amiami nwmp ON (nwmp.internal_metering_point_id=ami.internal_metering_point_id)
LEFT JOIN NW_UNIT_CONFIG NUC ON (NUC.UNIT_NR=AMI.UNIT_NR)
LEFT JOIN CFG_CONFIGURATION CFG ON ( nuc.configuration_id=CFG.CONFIGURATION_ID)
LEFT JOIN CFG_REGISTER CR ON (CR.CONFIGURATION_ID=NUC.CONFIGURATION_ID AND CR.CONFIGURATION_ID=CFG.CONFIGURATION_ID)
WHERE ami.unit_type=58 and ami.input_nr in (1,2) and ami.valid_until is null and nuc.valid_until is null and CR.REGISTER_TYPE=8 and CR.register_nr in(5,6) ;

I need to find unit_nr not having register_nr=6 as it then will have only register_nr=5 and not both . If it has both I do not need to find it.

Answer 1

You can use the COUNT analytic function with a CASE expression:

SELECT unit_type,
       unit_nr,
       metering_point_id,
       register_name
FROM   (
  SELECT ami.unit_type,
         ami.unit_nr,
         nwmp.metering_point_id,
         CR.register_name,
         COUNT(CASE cr.register_nr WHEN 5 THEN 1 END) OVER (PARTITION BY ami.unit_nr) AS num5,
         COUNT(CASE cr.register_nr WHEN 6 THEN 1 END) OVER (PARTITION BY ami.unit_nr) AS num6
  from   nw_metering_connection@AMIAMI AMI
         LEFT JOIN NW_METERING_POINT@amiami nwmp ON (nwmp.internal_metering_point_id=ami.internal_metering_point_id)
         LEFT JOIN NW_UNIT_CONFIG NUC ON (NUC.UNIT_NR=AMI.UNIT_NR)
         LEFT JOIN CFG_CONFIGURATION CFG ON ( nuc.configuration_id=CFG.CONFIGURATION_ID)
         LEFT JOIN CFG_REGISTER CR ON (CR.CONFIGURATION_ID=NUC.CONFIGURATION_ID AND CR.CONFIGURATION_ID=CFG.CONFIGURATION_ID)
  WHERE  ami.unit_type=58
  AND    ami.input_nr in (1,2)
  AND    ami.valid_until is null
  AND    nuc.valid_until is null
  AND    CR.REGISTER_TYPE=8
  AND    CR.register_nr in(5,6)
)
WHERE num5 > 0
AND   num6 = 0;