How to IFNULL or COALESCE rewrite in a SQL query?
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 ;