NOT HAVING (inverse HAVING) clause with aggregation in Oracle

Question

I am trying to filter the results of a GROUP BY CUBE using the HAVING clause. However I need to keep rows that do not meet a combination of conditions.

I intuitively tried:

SELECT [...]
FROM [...]
NOT HAVING (flag_1 = 1 AND flag_2 = 1 AND flag_3 = 1)
GROUP BY CUBE [...]

Sadly Oracle doesn't recognize NOT HAVING as valid syntax.

From a mathematical standpoint, inversing each individual condition does not yield the same result :

HAVING (flag_1 != 1 AND flag_2 != 1 AND flag_3 != 1)

How can I achieve the logical equivalent of NOT HAVING ?

Note: I found an existing question that was somewhat related, but it was specific to Microsoft Access and the goal was not the same, hence this new question­.

Answer 1

The mathematical inverse of your HAVING clause requires that you change the AND's to OR's and, if columns are nullable, null-check as well.

EG (if nulls are possible):

HAVING (nvl(flag_1,1) != 1 OR NVL(flag_2,1) != 1 OR NVL(flag_3,1) != 1) 

Answer 2

Well, I thought about adapting a similar method based on CASE that I sometimes use inside a WHERE. It seems that CASE works inside HAVING as well !

Solution:

SELECT [...]
FROM [...]

HAVING
    CASE
        WHEN flag_1 = 1 AND flag_2 = 1 AND flag_3 = 1
        THEN 0
        ELSE 1
    END = 1

GROUP BY CUBE [...]

Answer 3

I think the simplest solution is to just change it to HAVING NOT ...

SELECT [...]
FROM [...]
HAVING NOT (flag_1 = 1 AND flag_2 = 1 AND flag_3 = 1)
GROUP BY CUBE [...]

But I often find NOT (...) unintuitive; as an alternative, @MichaelBroughton's answer explains how to invert the logic from NOT (x AND y) to NOT x OR NOT y

Answer 4

This is the same as Michael Broughton's answer and I hate to duplicate but I thought it could be clearer. If he wants to incorporate any of this into his answer, I'm happy to delete this one.

To determine the logical equivalent of NOT (boolean_expression), you can apply De Morgan's laws. See: https://en.wikipedia.org/wiki/De_Morgan%27s_laws

Basically, you reverse the logic of each term and change all ANDs to ORs and all ORs to ANDs. So,

NOT (A AND B AND C) ==> (NOT A OR NOT B OR NOT C)

But you need to keep track of nulls too. Here's the process:

Starting with...

HAVING NOT (flag_1 = 1 AND flag_2 = 1 AND flag_3 = 1)

First, add the implicit assumptions about NULLs that exist in the starting expression. E.g., if flag_1=1, then it is, of course, not NULL.

HAVING NOT (flag_1 = 1 AND flag_1 IS NOT NULL 
        AND flag_2 = 1 AND flag_2 IS NOT NULL 
        AND flag_3 = 1 AND flag_3 IS NOT NULL)

Now, apply the first part of De Morgan's laws and reverse the logic of each term. So, e,g., flag_1 = 1 becomes flag_1 != 1...

HAVING (flag_1 != 1 AND flag_1 IS NULL 
    AND flag_2 != 1 AND flag_2 IS NULL 
    AND flag_3 != 1 AND flag_3 IS NULL)

Finally, apply the second part of De Morgan's laws and switch all ANDs->ORs and vice versa...

HAVING (flag_1 != 1 OR flag_1 IS NULL 
     OR flag_2 != 1 OR flag_2 IS NULL 
     OR flag_3 != 1 OR flag_3 IS NULL)

And there is your answer.

How about just NOT (flag_1 = 1 AND flag_2 = 1 AND flag_3 = 1)?

This does not work because it does not handle null values as one would expect. In Oracle, any comparison with null is false. So,

    (1 = NULL) ... false
NOT (1 = NULL) ... also false

So a row with flag_1 and flag_2 both equal to 1 but flag_3 null would NOT show up in your results.