Re: RPG with embedded SQL -- RPG400-L

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.