When single row not null then return nothing with Oracle SQL-CodePudding

When querying a table in Oracle Dev I need to ignore any ID that doesn't have a null date.

An ID can be returned multiple times e.g. 5 times, 4 can have a null date but one might have 01.01.2022 in which case all of them need to be ignored.

Here is the SQL:

SELECT 
ID,
ACTIVATION_DATE,
ACCEPTED_DATE
FROM TABLE
WHERE ACCEPTED_DATE IS NOT NULL 
--AND ACTIVATION_DATE IS NULL 
AND ID IN ('AA1','AA2');

And the result:

ID	ACTIVATION_DATE	ACCEPTED_DATE
AA1		01/04/2022
AA1		15/03/2022
AA1	22/08/2022	07/06/2022
AA1		11/05/2022
AA2		06/06/2022
AA2	25/09/2022	12/12/2021

You can see AA1 has pulled 4 rows but because it has one activation date, they now all need to be ignored. If I leave AND ACTIVATION_DATE IS NULL in there it will still return the blank rows, which need to be ignored altogether.

Assume this will need to be a subquery? Any help would be much appreciated!

I have tried the SQL query as above

CodePudding user response：

Use the COUNT analytic function:

SELECT id,
       activation_date,
       accepted_date
FROM   (
  SELECT id,
         activation_date,
         accepted_date,
         COUNT(activation_date) OVER (PARTITION BY id) AS num_active
  FROM   table_name
  WHERE  accepted_date IS NOT NULL 
  AND    id IN ('AA1','AA2')
)
WHERE  num_active = 0;

CodePudding user response：

I'd try with not exists:

Sample data:

SQL> WITH
  2     table1 (id, activation_date, accepted_date)
  3     AS
  4        (SELECT 'AA1', NULL             , DATE '2022-04-01' FROM DUAL UNION ALL
  5         SELECT 'AA1', NULL             , DATE '2022-03-15' FROM DUAL UNION ALL
  6         SELECT 'AA1', DATE '2022-08-22', DATE '2022-06-07' FROM DUAL UNION ALL
  7         SELECT 'AA2', DATE '2022-09-25', DATE '2021-12-12' FROM DUAL)

Query begins here:

  8  SELECT id, activation_date, accepted_date
  9    FROM table1 a
 10   WHERE     id IN ('AA1', 'AA2')
 11         AND NOT EXISTS
 12                (SELECT NULL
 13                   FROM table1 b
 14                  WHERE     b.id = a.id
 15                        AND b.activation_date IS NULL);

ID  ACTIVATION ACCEPTED_D
--- ---------- ----------
AA2 25/09/2022 12/12/2021

SQL>

AA1 is excluded from result as it contains NULL values in activation_date. AA2 is being returned as its activation_date isn't empty.