Re: RPG with embedded SQL

What's the SQLCODE and/or SQLSTATE after the delete?

It should be easy to run in debug, stopping after the delete to ensure
that the records are gone.

For that matter, if there weren't I'd assume the RPG write would fail
due to a duplicate key.

I'd guess your calculations have a bug.

Charles

On Fri, Jul 8, 2011 at 10:05 AM, <KBushard@xxxxxxxxxxxxxxxx> wrote:

Dear All:

I'm having a problem with some embedded SQL and thought I had it working;
now I'm seeing the problem again.

The program is supposed to recalculate physical inventory count statistics
for the cycle whenever a batch is posted.  I have a file of history which
is retained for a couple of cycles.  I recalculate because there could be
items "found" in the batch being posted that were "not found" in a
previous batch and now will calculate differently.

I've abbreviated the code (I hope not too much) but basically, I am
deleting all the statistics for the cycle, recalculating, and (re)
writing, which now includes the batch that was just posted.

It's acting like the delete isn't working, and doubles-up the statistics.
I have used the "Set Option Closqlcsr = *Endmod" in other programs to fix
open cursor issues if the job runs again, but it's not working here.

Here's my RPG program, abbreviated, but I've included all the sql
statements:


      Exec Sql
        Set Option Closqlcsr = *Endmod;

      chain ($_strwh:$_whse#:' ') picyclectl;
      if %found(picyclectl);
        exsr #deleteStats;
        exsr #calcStats;
      endif;

      *inlr = *on;
      //*************************************************************
      //  delete stats for this store/warehouse/cycle               *
      //*************************************************************
      begsr #deleteStats;
        Exec Sql
          Delete From PIAISLESTS
            Where STRWH = :$_STRWH And
                  WHSE# = :$_WHSE# And
                  PIYEAR = :CYC_PIYEAR And
                  PICYCLE# = :CYC_PICYCLE#;
        Exec Sql
          Commit;
      endsr;
      //*************************************************************
      //  process a store/warehouse                                 *
      //*************************************************************
      begsr #calcStats;
        clear sts.sts_info;

        Exec Sql
          Declare Z@ Cursor For
            Select *
              From IVPILOCCYC
              Where STRWH = :$_STRWH And
                    WHSE# = :$_WHSE# And
                    PIYEAR = :CYC_PIYEAR And
                    PICYCLE# = :CYC_PICYCLE#
              For Read Only;
        Exec Sql
          Open Z@;

        dou sqlcode <> *zeros;
          Exec Sql
            Fetch Next From Z@ Into :IVPILOCCYCDS;
          if sqlcode = *zeros;

            ...   calculate stuff here   ...

          endif;
        enddo;

        // write the totals
        exsr #writetotals;

        Exec Sql
          Close Z@;
      endsr;
      //*************************************************************
      //  write the totals                                          *
      //*************************************************************
      begsr #writetotals;

         ....   set up data to be written   .......

        for x = 1 to %elem(bch.bch_info);
            als_pizone    = bch.aisle(x);
            als_pidatecls = bch.datecls(x);
            write piaislest#;
        endfor;
      endsr;
      //*************************************************************

Any advice/assistance is most appreciated!

Karleen Bushard
System i Software Devloper
HOM Furniture, Inc & Gabberts
kbushard@xxxxxxxxxxxxxxxx
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.