This statement inserts one row. If I execute the select seperately, it
returns over four thousand rows.
The only thing I can think of is that the WHERE statement is being
reevaluated when each row is returned, resulting in no results after the
first row is inserted into INVC_GRANTEE_INVT.
Is this what is happening? Is there any way around it besides using a
stored procedure and performing an initial select in order to get the
MAX to pass to the INSERT INTO SELECT?
BTW, doing it that way does work (the statement just returned this
moment)

N.B. the number 179 is not hardcoded, but a parameter.

INSERT INTO
INVC_GRANTEE_INVT (GRANTEE_INVOICE, CHC, PHARMACY, GROUPID, NDC,
START_QTY, CLM_QTY, RPL_QTY, BO_QTY)
SELECT
179, CHC, PHARMACY, GROUPID, NDC, END_QTY, 0, 0, 0
FROM
INVC_GRANTEE_INVT
WHERE
END_QTY <> 0 AND
/* get the last inventory statement for this grantee */
GRANTEE_INVOICE = (
SELECT
MAX(invt.GRANTEE_INVOICE)
FROM
INVC_GRANTEE_INVT invt
JOIN INVC_GRANTEE_INVC invc
ON invc.ID = invt.GRANTEE_INVOICE
WHERE
/* from the given invoice, get the grantee */
invc.GRANTEE = (
SELECT
GRANTEE
FROM
INVC_GRANTEE_INVC
WHERE
ID = 179 ));