RE: RPG with embedded SQL

Karleen,

You're deleting from your PIAISLETS output file then summarizing from your IVPILOCCYC input file to PIAISLETS again. Aren't the statistics doubled up on IVPILOCCYC for both batches? Should you delete the statistics from the previous batch from IVPILOCCYC or only select the current batch from IVPILOCCYC?

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment


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