How does this NOT IN in a Having statement in Oracle work

Question

I'm trying to rewrite an old Oracle SQL query I inherited and there is one part I don't understand. My new version comes up with about a dozen extra records and I've concluded it's because the HAVING clause in the original version is filtering them out(I removed the HAVING statement from my new query). I don't understand what the 'NOT IN (99, -99) is doing. If I remove the NOT IN I get an 'ORA-00920: invalid relational operator" error. If I change the 99, -99 to any other number the records still get filtered out.

Does anyone understand what the NOT IN (99, -99) is doing? I've never seen anything like this before.

   HAVING
   (sum(case 
   when (BD.EXPERIENCE_RATING_DESC) = 'RATED' 
   AND BD.BENEFIT_CATEGORY<>'INTEREST' 
   THEN CFCT.REPORTED_AMOUNT ELSE 0.00 END) 
   NOT IN (99,-99)
   AND sum(case 
   when (BD.EXPERIENCE_RATING_DESC) = 'POOLED' 
   AND BD.BENEFIT_CATEGORY<>'INTEREST' 
   THEN CFCT.REPORTED_AMOUNT ELSE 0.00 END) 
   NOT IN (99,-99)

Answer 1

If either SUM equals 99 or -99, filter that (aggregated) row out.

It may make more sense if you add the SUM values to your SELECT list if they are not there already, and then (temporarily) reverse the HAVING clause by removing the word "NOT" in both cases. The results should all be rows that have SUM values of either 99 or -99.

Answer 2

It means that sum of case expressions can be anything except 99 and -99.

Your code, simplified to Scott's EMP table: these are summaries of salaries per department:

SQL> select deptno, sum(sal) sumsal
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO     SUMSAL
---------- ----------
        10       8750
        20      10875
        30       9400

This is what you have, just (as I said) simplified: I want to omit values specified in line #4:

SQL> select deptno, sum(sal) sumsal
  2  from emp
  3  group by deptno
  4  having sum(sal) not in (8750, 9400)  --> this
  5  order by deptno;

    DEPTNO     SUMSAL
---------- ----------
        20      10875

SQL>

As you can see, result contains anything but values being specified.

Answer 3

An IN list is just a list of values to compare the other side to

You would normally see something like

MY_COL in (1,2,3)

in a WHERE clause which would only return rows where MY_COL was 1, 2 or 3.

Your statement is in a HAVING clause so it is still expecting a predicate but simply

sum(case 
   when (BD.EXPERIENCE_RATING_DESC) = 'RATED' 
   AND BD.BENEFIT_CATEGORY<>'INTEREST' 
   THEN CFCT.REPORTED_AMOUNT ELSE 0.00 END) 

is being checked against 99 and -99 and if it either of those values the row is dropped.

If you take out the not in you get an error as you your predicate just becomes sum(...) with nothing to compare it to.

Answer 4

Looks like you can clean up your having clause a little bit. Doing so might make the role of not in more obvious

where bd.benefit_category <> 'INTEREST'
group by...
having sum(case when bd.experience_rating_desc in ('RATED','POOLED') then cfct.reported_amount end) not in (99,-99);