It's possible that your delete isn't executing like you think. You should check the status of SQLCOD or SQLSTATE and see what the value is. Dump the program if you have too when sqlcod <> 0. Could it possible that someone has a record locked when the delete runs? This would cause the delete to fail.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of KBushard@xxxxxxxxxxxxxxxx
Sent: Friday, July 08, 2011 10:05 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG with embedded SQL
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.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
