Hi Rick!

You say that "179 is not hardcoded, but a parameter". 179 is mentioned
twice within your query. I think I once heard something like
parametres being more placement parametres than named parametres, and
that they actually were different parametres even if they had the same
name. Hence, my first advice would be for you to name them differently
every place in your query and set them separately.

Though, I don't know whether this will bring you any closer to your
solution. If it doesn't work, try this query (if this isn't basically
your query transformed to my coding style, then I've done something
wrong):

INSERT INTO
INVC_GRANTEE_INVT (GRANTEE_INVOICE, CHC, PHARMACY, GROUPID, NDC,
START_QTY, CLM_QTY, RPL_QTY, BO_QTY)
SELECT
:Param1, igi.CHC, igi.PHARMACY, igi.GROUPID, igi.NDC, igi.END_QTY, 0,
0, 0
FROM
INVC_GRANTEE_INVT igi
WHERE
igi.END_QTY <> 0 AND
/* without any later inventory statement for this grantee */
not exists (
select *
FROM
INVC_GRANTEE_INVT invt
JOIN INVC_GRANTEE_INVC invc
ON invc.ID = invt.GRANTEE_INVOICE
join INVC_GRANTEE_INVC igi2 /*join is preferable to a subselect */
on igi2.GRANTEE = invc.GRANTEE
where
igi.GRANTEE_INVOICE > invt.GRANTEE_INVOICE and
igi2.ID = :Param2 )

Actually, I didn't understand what this query was supposed to do until
after I had rewritten it! I expect 'my' version not only to work, but
also to execute quicker than your original query. Though as far as I
can see, provided you do use two and not one parameter, the result
should be identical.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> 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 ));