How to IFNULL or COALESCE rewrite in a SQL query? I there a way to rewrite a SQL query, so I can avoid IFNULL or COALESCE, because the HXTT DBF JDBC driver (supports SQL92) throws the following error.:

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.


The following query causes the above error.:

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
    FROM tckopf AS shipManagementInvoice
    WHERE shipManagementInvoice.referenzid = 1
        AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
    FROM tckopf AS shipManagementCreditNote
    WHERE shipManagementCreditNote.referenzid = 1
        AND shipManagementCreditNote.btyp = 6 )
 AS shipManagementResult


If I remove IFNULL or COALESCE, there is no error.
There are several ways to rewrite the query and even more because both subqueries use the same base table. Not sure why the error is thrown and who is to blame, the JDBC drivers, the Foxpro or something else, so here are a few alternatives:


(1) using one query for both searches:

SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                         WHEN t.btyp = 6 THEN - t.netto 
                                         ELSE 0 
                    END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

(2) pulling the COALESCE() or IFNULL() function call in the external level, either using the original query:

SELECT 
  COALESCE( ( SELECT SUM(i.netto) 
              FROM tckopf AS i
              WHERE i.referenzid = 1
                AND i.btyp = 5 
            ), 0)
- COALESCE( ( SELECT SUM(c.netto) 
              FROM tckopf AS c
              WHERE c.referenzid = 1
                AND c.btyp = 6 
            ), 0)
  AS shipManagementResult ;

(2b) or the above (1st) query:

SELECT COALESCE( 
  ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                    WHEN t.btyp = 6 THEN - t.netto 
                                    ELSE 0 
                END)
    FROM tckopf AS t
    WHERE t.referenzid = 1
  ), 0) AS shipManagementResult ;