Re: A Mystery SQL Problem

[Dan <dan27649@xxxxxxxxx>]

A real WAG:  Try changing the "count" variable to another name, like
counteffyear.  Just wondering if "count" is reserved.

- Dan

On Fri, Dec 2, 2016 at 5:20 PM, Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:

> I have a real mystery.  I am having problems with an SQL INSERT.
>
> I am performing maintenance on a file and then extracting from that file
> and inserting the new records into another file.  Here is the code:
>
> exec sql
>     (SELECT count(*) into :count
>        FROM ahpovrp
>        WHERE ROEFDT = :effective_year);
>
>  exec sql
>      INSERT INTO MMNYOVRP
>            (SELECT * FROM ahpovrp
>               WHERE ROEFDT = :effective_year
>               ORDER BY rocomp, rostcd, roplan, robenp);
>
> When I execute the first SQL statement, COUNT is 100; but when the second
> statement the file MMNYOVRP contains 140 records.
>
> I don't understand how this can be. Any ideas?
>
> Commitment control is off. The file I extract from is closed at this point.
>
> Marvin