ZeroK ZeroK - 4 months ago 8
SQL Question

How do I prevent XMLAGG from creating a row in a subquery that returns no rows?

So, here's the issue: I'm using XMLAGG to roll up values into a list (to generate an html table in the output), using a sub-query. Without an XMLAGG the sub-query returns zero rows (as expected), but when I include the XMLAGG it returns a row, even though the result of the XMLAGG is Null. Is there a systematic way to prevent this, or do I just have to wrap the whole thing in a case statement and avoid executing the XMLAGG when it is empty?

Code looks like this:

SELECT 'stuff1' outerquery
, (SELECT TO_CLOB('some text')
|| xmlcast(xmlagg(xmlelement(E, 'text' || table2.data1 || ' text2' || table3.data1) ORDER BY table2.due_date) AS CLOB)
FROM table2
JOIN table3 ON table2.table3_id = table3.table3_id
WHERE [conditions]
) subquery
FROM table1
WHERE [conditions]


where the conditions result in one row for the outer query and no rows for the inner query. When I run this the "some text" portion displays in the sub-query, even though it should have no rows, and it is correctly null when I remove the XMLAGG without changing the conditions.

Has anyone dealt with this issue before?

(I'm using XMLAGG instead of LISTAGG because I expect the final result will go over 4000 characters)

This is in an Oracle database.

If you want to run it to test, I was able to demonstrate this behavior using just the DUAL table. Comment out the XMLAGG line to see the difference.

SELECT 'stuff1' outerquery
, (SELECT TO_CLOB('some text')
|| xmlcast(xmlagg(xmlelement(E, 'text') ORDER BY 1) AS CLOB)
FROM DUAL
WHERE 1=2
) subquery
FROM DUAL

Answer

This isn't specific to xmlagg(). In any database, a query that has aggregation functions is an aggregation query. If there is no group by, then the query always returns exactly one row. This is a property of SQL in general.

What can you do? Here are some suggestions:

You can add a GROUP BY. That will not return rows if there are no matches. This is sort of hard to do if you are using a subquery in the SELECT.

You can use a subquery and a WHERE clause to see if any values are found.

You can also use a HAVING clause: HAVING COUNT(*) > 0. I'm not sure if this works in all databases.