I am getting an SQLCODE=-119 in DB2

Question

I was converting MySql to DB2 queries, and so far I got to this:

SELECT
    loandata.*,
    SUM(lc.amount_outstanding_derived) AS chargesDue
FROM
    (
    SELECT
        cl.display_name AS clientName,
        cl.id AS clientId,
        ln.id AS loanId,
        ln.account_no AS accountId,
        ln.loan_status_id AS accountStatusId,
        pl.short_name AS productShortName,
        ln.product_id AS productId,
        ln.currency_code AS currencyCode,
        ln.currency_digits AS currencyDigits,
        ln.currency_multiplesof AS inMultiplesOf,
        rc.name AS currencyName,
        rc.display_symbol AS currencyDisplaySymbol,
        rc.internationalized_name_code AS currencyNameCode,
        CASE WHEN ln.loan_status_id = 200 THEN ln.principal_amount
        ELSE NULL
END AS disbursementAmount,
    SUM(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_amount ELSE 0.0 END), 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.principal_completed_derived ELSE 0.0 END, 0.0)) AS principalDue,
    ln.principal_repaid_derived AS principalPaid,
    SUM(COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.interest_amount ELSE 0.0 END, 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.interest_completed_derived ELSE 0.0 END, 0.0)) AS interestDue,
    ln.interest_repaid_derived AS interestPaid,
    SUM(COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_amount ELSE 0.0 END, 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_completed_derived ELSE 0.0 END, 0.0)) AS feeDue,
    ln.fee_charges_repaid_derived AS feePaid
FROM
    m_loan LN
JOIN m_client cl ON
    cl.id = ln.client_id
LEFT JOIN m_office OF ON
    of.id = cl.office_id
    AND of.hierarchy LIKE ?
LEFT JOIN m_product_loan pl ON
    pl.id = ln.product_id
LEFT JOIN m_currency rc ON
    rc.code = ln.currency_code
JOIN m_loan_repayment_schedule ls ON
    ls.loan_id = ln.id
    AND ls.completed_derived = 0
    AND ls.duedate <= ?
WHERE
    of.id = ?
    AND (ln.loan_status_id = 300)
    AND ln.group_id IS NULL
)  AS loandata
LEFT JOIN m_loan_charge lc ON
    lc.loan_id = loandata.loanId
    AND lc.is_paid_derived = 0
    AND lc.is_active = 1
    AND ( lc.due_for_collection_as_of_date <= ?
    OR lc.charge_time_enum = 1)
GROUP BY
    loandata.clientId,
    loandata.loanId
ORDER BY
    loandata.clientId,
    loandata.loanId

But I am getting an error :

SQL Error [42803]: An expression starting with "DISBURSEMENTAMOUNT" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.. SQLCODE=-119, SQLSTATE=42803, DRIVER=4.26.14

I have tried inserting Group by and Order By clauses each for the select clause items like so:

SELECT
    loandata.*,
    SUM(lc.amount_outstanding_derived) AS chargesDue
FROM
    (
    SELECT
        cl.display_name AS clientName,
        cl.id AS clientId,
        ln.id AS loanId,
        ln.account_no AS accountId,
        ln.loan_status_id AS accountStatusId,
        pl.short_name AS productShortName,
        ln.product_id AS productId,
        ln.currency_code AS currencyCode,
        ln.currency_digits AS currencyDigits,
        ln.currency_multiplesof AS inMultiplesOf,
        rc.name AS currencyName,
        rc.display_symbol AS currencyDisplaySymbol,
        rc.internationalized_name_code AS currencyNameCode,
        CASE WHEN ln.loan_status_id = 200 THEN ln.principal_amount
        ELSE NULL
END AS disbursementAmount,
    SUM(COALESCE((CASE WHEN ln.loan_status_id = 300 THEN ls.principal_amount ELSE 0.0 END), 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.principal_completed_derived ELSE 0.0 END, 0.0)) AS principalDue,
    ln.principal_repaid_derived AS principalPaid,
    SUM(COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.interest_amount ELSE 0.0 END, 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.interest_completed_derived ELSE 0.0 END, 0.0)) AS interestDue,
    ln.interest_repaid_derived AS interestPaid,
    SUM(COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_amount ELSE 0.0 END, 0.0) - COALESCE(CASE WHEN ln.loan_status_id = 300 THEN ls.fee_charges_completed_derived ELSE 0.0 END, 0.0)) AS feeDue,
    ln.fee_charges_repaid_derived AS feePaid
FROM
    m_loan LN
JOIN m_client cl ON
    cl.id = ln.client_id
LEFT JOIN m_office OF ON
    of.id = cl.office_id
    AND of.hierarchy LIKE ?
LEFT JOIN m_product_loan pl ON
    pl.id = ln.product_id
LEFT JOIN m_currency rc ON
    rc.code = ln.currency_code
JOIN m_loan_repayment_schedule ls ON
    ls.loan_id = ln.id
    AND ls.completed_derived = 0
    AND ls.duedate <= ?
WHERE
    of.id = ?
    AND (ln.loan_status_id = 300)
    AND ln.group_id IS NULL
GROUP BY 
        cl.display_name,
        cl.id,
        ln.id,
        ln.account_no,
        ln.loan_status_id,
        pl.short_name,
        ln.product_id,
        ln.currency_code,
        ln.currency_digits,
        ln.currency_multiplesof,
        rc.name,
        rc.display_symbol,
        rc.internationalized_name_code,
        ln.loan_status_id,
        ln.principal_amount,
        ln.principal_repaid_derived,
        ln.interest_repaid_derived,
        ln.fee_charges_repaid_derived
ORDER BY 
        cl.display_name,
        cl.id,
        ln.id,
        ln.account_no,
        ln.loan_status_id,
        pl.short_name,
        ln.product_id,
        ln.currency_code,
        ln.currency_digits,
        ln.currency_multiplesof,
        rc.name,
        rc.display_symbol,
        rc.internationalized_name_code,
        ln.loan_status_id,
        ln.principal_amount,
        ln.principal_repaid_derived,
        ln.interest_repaid_derived,
        ln.fee_charges_repaid_derived
    )  AS loandata
LEFT JOIN m_loan_charge lc ON
    lc.loan_id = loandata.loanId
    AND lc.is_paid_derived = 0
    AND lc.is_active = 1
    AND ( lc.due_for_collection_as_of_date <= ?
    OR lc.charge_time_enum = 1)
GROUP BY
    loandata.clientId,
    loandata.loanId
ORDER BY
    loandata.clientId,
    loandata.loanId

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-04-06T00:59:31

A gross simplification of your query is:

SELECT loandata.*,
        SUM(lc.amount_outstanding_derived) AS chargesDue
FROM (SELECT <lots of columns>
      FROM <lots of tables>
      . . .
     ) loandata LEFT JOIN
     m_loan_charge lc
     ON lc.loan_id = loandata.loanId AND
        lc.is_paid_derived = 0 AND
        lc.is_active = 1 AND
        ( lc.due_for_collection_as_of_date <= ? OR lc.charge_time_enum = 1)
GROUP BY loandata.clientId, loandata.loanId
ORDER BY loandata.clientId, loandata.loanId;

You have a SELECT * bringing in lots of columns. Fine. Then you have an aggregation function -- so the query is an aggregation query. Need a GROUP BY with lots of columns.

But I don't find one. I only find a GROUP BY with two columns. And that is an error in SQL. And happily, that is now an error in the more recent versions of MySQL. And in basically every other database.

You need to list all the columns in the GROUP BY that are not aggregated in the SELECT.

I am getting an SQLCODE=-119 in DB2

1 Answers