I have an invoice number. From that, I can get what entity is being invoiced:
/* from the given invoice, get the grantee */
invc.GRANTEE = (
SELECT
igi2.GRANTEE
FROM
INVC_GRANTEE_INVC igi2
WHERE
igi2.ID = :INVOICE ) /* invoice number 179 */

Now that I have that, I can get from another table the last inventory snapshot for that entity:
/* get the last inventory statement for this grantee */
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 = [the previous result]

Basically I'm trying to get the last group of data from INVC_GRANTEE_INVT and use it as the starting data for the next group I'm inserting into INVC_GRANTEE_INVT.
I went with the stored procedure, and hopefully this will get fixed soon. Getting bad data out of a database gives me the willies.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Friday, May 20, 2005 6:17 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: INSERT INTO SELECT only inserts one row

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 ));




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links