Following is a REPRO of a problem I had:

  
-SET &ECHO=ALL;
-* File modprob.fex
-DEFAULT &DB=SQLMSS
-*APP HOLD BASEAPP 
-*TABLE FILE CAR
-*SUM SALES RETAIL_COST DEALER_COST
-*BY CAR BY MODEL
-*WHERE MODEL OMITS '2000'
-*ON TABLE HOLD AS MODPROB FORMAT &DB
-*END
-*-RUN
-*EX -LINES 2 EDAPUT ACCESS,MODPROB,C,FILE
-*SEGNAME=SEG01,  TABLENAME=MODPROB, KEYS=0, WRITE=YES, $
-*-EXIT
-* 2
TABLE FILE CAR
SUM SALES RETAIL_COST 
-*DEALER_COST 
COMPUTE DEALER_COST/I2=2; 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'DOOR'
ON TABLE HOLD AS MODINP
END
-RUN
-* 3
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END
-RUN
-* 4
TABLE FILE CAR
SUM SALES RETAIL_COST DEALER_COST 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'AUTO'
ON TABLE HOLD AS MODINP
END
-RUN
-* 5
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END

Notes:
1. Create a database table. This is done once, then the ACX file is replaced for KEYS=0 and the KEYS in the database are deleted.
2. Create input to the table with a field having a wrong format.
3. When updating the table, you get an error.
4. Create a "good" input.
5. When updating the table, all records are rejected.
See ECHO:

  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 -*DEALER_COST
 COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 ERROR AT OR NEAR LINE     27  IN PROCEDURE modprob FOCEXEC *
 (FOC437) THE FORMAT USED WITH FIXFORM IS INCOMPATIBLE WITH THE DATA FORMAT
 BYPASSING TO END OF COMMAND
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     0  REJECTED=     8
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0

However when all inputs are "good" then it works:

  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 DEALER_COST
 -*COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =    12  ACCEPTED=    12  REJECTED=     0
 SEGMENTS:             INPUT =    12  UPDATED =     0  DELETED =     0
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     8  REJECTED=     0
 SEGMENTS:             INPUT =     8  UPDATED =     0  DELETED =     0

Anybody has an idea how to circumvent this problem?

[maybe]Use a different HOLD file name for each step.[/maybe]

Francis,
No such luck...

Danny - My first hunch is that after the "bad" transaction, your database or database connection is left in some sort of inconsistent state (and yes I know that's very vague).

What is your AUTOCOMMIT setting on your database connection? You could try doing something like this:

  
SQL SQLMSS SET AUTOCOMMIT     ON FIN;

-*  Do your 1st Modify
-*  Check &FOCERRNUM.  If 0 proceed to your next MODIFY.  Otherwise do a rollback:
SQL SQLMSS
ROLLBACK [WORK] ;
END
-*  Next, do you want to do your 2nd MODIFY even if the first fails?  If so, go ahead.
-*  Finally, when you're all done, do a commit:
SQL SQLMSS
COMMIT ;
END

I assumed this is a FOCUS DB. Perhaps you can try WebFOCUS COMMIT within the MODIFY.

MODIFY FILE NXMBRRSE
 COMMIT 1
 FIXFORM FROM TBRRDTAY
  MATCH USERID
   ON NOMATCH REJECT
   ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
 DATA ON TBRRDTAY
END

Francis,
SET SHADOW=ON
and use of COMMIT...
nope!

Eric,
No luck either...

quote:

[maybe]Use a different HOLD file name for each step.[/maybe]

...or toss in a CHECK FILE and ?FF right after creating each Hold file.

Maybe add a COMPUTE after the FIXFORM in each MODIFY?

FIXFORM FROM MODINP
COMPUTE DEALER_COST/D7 = DEALER_COST ;

Its been a while, but

Instead of issuing COMMIT 1, use ON MATCH COMMIT or Call a subroutine with a COMMIT on MATCH.

I seem to remember doing this in the 80's

If your first MODIFY fails because of the wrong format you could just set a flag. Use that to loop back to the code that recreates the database and from there to the second MODIFY - assuming you are always creating a new database each time.

But assuming you want the data from both passes why not just use a DEFINE to reformat the problematic DEALER COST right from the get go, which would ensure you don't have any incorrect data types at the MODIFY stage.