Re: RPG with embedded SQL

Just some questions on your approach:

Why mix embedded SQL and native I/O: SQL for delteting records and
retreiving data and native I/O for writing?
Could you not use SUM, COUNT and AVG in your SQL statement?

Back to your question/problem:
Is this not a COMMIT CONTROL-issue?

Otherwise I would just do a CLRPFM before the call of the programme. But
then I am a simple diehard, doing things the old fashioned way.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 8-7-2011 at 9:05 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!