RE: Update Operation Failure Help Please

Geez...not an answer to your question directly but this is really
ugly...why not just use native I/O on the ssw85 file updates(which
incidentally could be causing your issue but I'm not sure on that...)

SQL IS a wonderful thing but use it where needed, not just because it's
there...not to mention it will run a lot faster since you're doing 1 I/O
compare to 3.

         SetLL *LOVAL ssw85;

         prevSCAC = '';

         prevTRLR = '';

         read ssw85;

         DoW not %eof();

            select;

              when trlstat = 'D' and ordersts <> 'R';

                ordersts = 'R';

              when trlstat = 'E' and ordersts <> 'R';

                ordersts = 'R';

              when trlstat = 'R' and ordersts <> 'R';

                ordersts = 'R';

            ENDSL;

         if suppname = *BLANKS;
         // use a LF on ssm01 keyed by sccd, sclc, scsnm ascending
           chain (suppcode:supploc) ssm01;
           If %found;
              suppname = scsnm;
           Else;
             suppname = *blanks;
          EndIf;

           ENDIF;

       if ordersts = *BLANK;

      /end-free

     C/Exec SQL

     C+ select count(*) into :ASNCOUNT from sst02

     C+ where asodtp =:ordertyp and asodno =:ordernum and asplcd = :plcd

     C/End-Exec

      /free

       if SQLCOD <> 0;

         ASNCOUNT = 0;

       ENDIF;

      /end-free

     C*

     C/Exec SQL

     C+ select count(*) into :INVCOUNT from sst37

     C+ where ivodtp =:ordertyp and ivodno =:ordernum  and ivplcd =
:plcd

     C+ and ivrvdt <> 0

     C/End-Exec

 

      /free

         if SQLCOD <> 0;

           INVCOUNT = 0;

         ENDIF;

         ORDCOUNT = ASNCOUNT - INVCOUNT;

         Select;

           When ORDCOUNT = ASNCOUNT;

             OSTATUS = 'O';

           When INVCOUNT < ASNCOUNT;

             OSTATUS = 'P';

           When ORDCOUNT = 0;

             OSTATUS = 'R';

         ENDSL;

        ordersts = OSTATUS;

       ENDIF;

         curSCAC = scaccode;

         curTRLR = trlrnum;

         if %trim(curscac)+%trim(curtrlr) =
%trim(prevSCAC)+%trim(prevTRLR);

           quarter = curQTR;

         else;

           idxQTR =
%lookup((%char(pseqrrn)+%trim(%char(pseqsub))):arrPRODSEQ);

           quarter = arrQTR(idxQTR);

           curQTR = quarter;

           prevSCAC = curSCAC;

           prevTRLR = curTRLR;

         ENDIF;

         update rssw85;

         read ssw85;

       Enddo; 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski
Sent: Friday, June 02, 2006 10:34 AM
To: RPG programming on the AS400 / iSeries
Subject: Update Operation Failure Help Please

Hi All,

 

Can someone give me a hand trying to figure out why I can't update a
record when data has changed?  In my code, I have an update operation
near the end of the loop.  Whenever my program hits this statement, I
get an "Update or delete in file SSW85 without prior input operation."
error. 

 

I know there are values changing because I have traced one of the values
to see if it changes.  For example, when I enter the loop, the field
"QUARTER" is blank.  However, after I do a %lookup on the index, the
value becomes "Q3".

 

Here's my current code:

         SetLL *LOVAL ssw85;

         prevSCAC = '';

         prevTRLR = '';

         read ssw85;

         DoW not %eof();

            select;

              when trlstat = 'D' and ordersts <> 'R';

                ordersts = 'R';

              when trlstat = 'E' and ordersts <> 'R';

                ordersts = 'R';

              when trlstat = 'R' and ordersts <> 'R';

                ordersts = 'R';

            ENDSL;

         if suppname = *BLANKS;

      /end-free

     C/Exec SQL

     C+ update SSW85 set suppname = (select min(scsnm) from SSM01

     C+ where concat(ssm01.sccd,ssm01.sclc)

     C+ = concat(ssw85.suppcode,ssw85.supploc))

     C+ where suppcode = :suppcode and supploc = :supploc

     C/End-Exec

     C*

      /free

           ENDIF;

       if ordersts = *BLANK;

      /end-free

     C/Exec SQL

     C+ select count(*) into :ASNCOUNT from sst02

     C+ where asodtp =:ordertyp and asodno =:ordernum and asplcd = :plcd

     C/End-Exec

      /free

       if SQLCOD <> 0;

         ASNCOUNT = 0;

       ENDIF;

      /end-free

     C*

     C/Exec SQL

     C+ select count(*) into :INVCOUNT from sst37

     C+ where ivodtp =:ordertyp and ivodno =:ordernum  and ivplcd =
:plcd

     C+ and ivrvdt <> 0

     C/End-Exec

 

      /free

         if SQLCOD <> 0;

           INVCOUNT = 0;

         ENDIF;

         ORDCOUNT = ASNCOUNT - INVCOUNT;

         Select;

           When ORDCOUNT = ASNCOUNT;

             OSTATUS = 'O';

           When INVCOUNT < ASNCOUNT;

             OSTATUS = 'P';

           When ORDCOUNT = 0;

             OSTATUS = 'R';

         ENDSL;

      /end-free

     C/Exec SQL

     C+ update ssw85 set ordersts = :OSTATUS

     C+ where plantcde = :plantcde and ordertyp=:ordertyp

     C+ and ordernum = :ordernum

     C/End-Exec

      /free

       ENDIF;

         curSCAC = scaccode;

         curTRLR = trlrnum;

         if %trim(curscac)+%trim(curtrlr) =
%trim(prevSCAC)+%trim(prevTRLR);

           quarter = curQTR;

         else;

           idxQTR =
%lookup((%char(pseqrrn)+%trim(%char(pseqsub))):arrPRODSEQ);

           quarter = arrQTR(idxQTR);

           curQTR = quarter;

           prevSCAC = curSCAC;

           prevTRLR = curTRLR;

         ENDIF;

         update rssw85;

         read ssw85;

       Enddo;

 

Thanks in advance for any ideas.  I'm sure it's something pretty
obvious, but I'm having trouble trying to figure out what it is.

 

Brian.

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

Ph: 705-435-7814 x343

Fx: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-