Oracle - Why I can't exclude rows where length(listagg(somecolumn)) > xxx

Question

I just can't seem to work this one out :(

I run a query that returns some results but in those results I need to filter out where this customer does not have freetext notes that are greater than 240 characters

Unfortunately the way the Data is stored is notes are split up across multiple rows as such:

NOTE_NUMBER    LINE_NUMBER    NOTE_TEXT
123            1              ABC
123            2              DEFGHIJ
456            1              ABCDEE

etc NOTE_NUMBER is the Primary Key back to another table I already worked out how to return one result row per NOTE_NUMBER

SELECT A.NOTE_NUMBER, LISTAGG(NOTE_TEXT)
FROM TABLE TABLE 1 A
JOIN TABLE 2 B ON A.NOTE_NUMBER = B.NOTE_NUMBER
GROUP BY A.NOTE_NUMBER

This returns me:

123 ABCDEFGHIJ
456 ABCDEE

fantastic! But now say I only want to to return rows where the LENGTH(LISTAGG(NOTE_TEXT)) < 7 heres where I am stumped.

I already saw this works to change my query to:

SELECT A.NOTE_NUMBER, LISTAGG(NOTE_TEXT), LENGTH(LISTAGG(NOTE_TEXT))
FROM TABLE TABLE 1 A
JOIN TABLE 2 B ON A.NOTE_NUMBER = B.NOTE_NUMBER
GROUP BY A.NOTE_NUMBER

now gives me:

123 ABCDEFGHIJ 11
456 ABCDEE 7

however if I want to put a where clause in so now:

SELECT A.NOTE_NUMBER, LISTAGG(NOTE_TEXT), LENGTH(LISTAGG(NOTE_TEXT))
FROM TABLE TABLE 1 A
JOIN TABLE 2 B ON A.NOTE_NUMBER = B.NOTE_NUMBER
WHERE LENGTH(LISTAGG(NOTE_TEXT)) < 7
GROUP BY A.NOTE_NUMBER

I get an error

ORA-00934 group function is not allowed here

so then my searching brought me using CASE:

SELECT A.NOTE_NUMBER, CASE WHEN LENGTH(LISTAGG(NOTE_TEXT)) < 7 THEN LISTAGG(NOTE_TEXT) END
FROM TABLE TABLE 1 A
JOIN TABLE 2 B ON A.NOTE_NUMBER = B.NOTE_NUMBER
GROUP BY A.NOTE_NUMBER

however this still returns 2 rows

123 NULL
456 ABCDEE

this will be no good to me when I try to do a not exists where length of note is > 7 characters because both rows will still return so the NOTE_NUMBER key will still match back to the other table

I tried to find out how can I exclude rows if that CASE is NULL and can't figure that out either!

All I need is this query to have returned is this:

456 ABCDEE

Please help me! Much Appreciated!

Answer 1

That's fairly simple - use query (that works) as a source (either as CTE or subquery) for another select statement which just uses what you've already collected:

WITH
   temp
   AS
      (  SELECT a.note_number,
                LISTAGG (note_text) note_text,
                LENGTH (LISTAGG (note_text)) note_len
           FROM table1 a JOIN table2 b ON a.note_number = b.note_number
       GROUP BY a.note_number)
SELECT *
  FROM temp
 WHERE note_len < 7

Answer 2

One option is to put your query in a CTE or subquery (I don't repeat that query here because the other answer shows it).

Another option is to use HAVING in your GROUP BY clause.

The reason why your attempt failed is that you can't use an aggregate function like LISTAGG in the WHERE clause.

Using HAVING, you can use it!

SELECT 
  t1.NOTE_NUMBER, 
  LISTAGG(t1.NOTE_TEXT) AS wholeText, 
  LENGTH(LISTAGG(t1.NOTE_TEXT)) AS wholeLength
FROM table1 t1
INNER JOIN table2 t2 
  ON t1.NOTE_NUMBER = t2.NOTE_NUMBER
GROUP BY t1.NOTE_NUMBER
  HAVING LENGTH(LISTAGG(t1.NOTE_TEXT)) < 7;

Try out both options on this sample fiddle