Focal Point
[SOLVED] Commit in MODIFY

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8161038482

May 20, 2008, 10:12 AM
roby
[SOLVED] Commit in MODIFY
Hy!!
How I can use the Commit in the modify?
I have a focus program with this instructions.

TABLE FILE MINNIE
PRINT ONE_FIELD
TWO_FIELD
BY ONE_FIELD NOPRINT
BY TWO_FIELD NOPRINT
ON TABLE SAVE AS ‘KEYDEL’
END
-*
MODIFY FILE ARXXXX
FIXFORM ONE_FIELD/9 TWO_FIELD/9
MATCH ONE_FIELD TWO_FIELD
ON NOMATCH REJECT
ON MATCH DELETE
ON MATCH GOTO CANCELLA
CASE CANCELLA
NEXT ONE_FIELD TWO_FIELD
ON NEXT DELETE
ON NEXT GOTO CANCELLA
ON NONEXT GOTO ENDCASE
ENDCASE
DATA ON KEYDEL
END

thanks for any help!!

This message has been edited. Last edited by: Kerry,
May 20, 2008, 10:54 AM
Francis Mariani
Here's an example of commit:

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



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 20, 2008, 10:57 AM
Francis Mariani
here's a commit for a MODIFY that updates a SQL table:

MODIFY FILE CHQ_IB_HIERARCHY
 COMPUTE TRN1/I9=; ACC1/I9=; NOM1/I9=; FOR1/I9=; INV1/I9=;
         DUP1/I9=; INP1/I9=; CHN1/I9=; DEL1/I9=;
 COMPUTE CM__COUNTER/I9=; CM__COM_TOT/D15=;
 FIXFORM FROM SQLIN ALIAS PROPAGATE
 TYPE AT END
" (ICM18372) -- stats for CHQ_IB_HIERARCHY --------------------------------------"
" (ICM18516) <TRN1 : Row(s) processed by ETL job"
" (ICM18514) <ACC1 : Row(s) accepted by target table"
" (ICM18515) <INP1 : Row(s) inserted into target table"
" (ICM18517) <CHN1 : Row(s) updated in target table"
" (ICM18518) <DEL1 : Row(s) deleted from target table"
" (ICM18519) <FOR1 : Row(s) rejected due to format errors"
" (ICM18520) <INV1 : Row(s) rejected due to validation errors"
" (ICM18521) <NOM1 : Row(s) rejected due to no match logic"
" (ICM18522) <DUP1 : Row(s) rejected because duplicate  exists"
"  "
 GOTO MATCHIT1
 
 CASE MATCHIT1
 COMPUTE
  ROOT_ID=E01;
  L1_OBJ_ID=E02;
  L2_OBJ_ID=E03;
  L3_OBJ_ID=E04;
  L4_OBJ_ID=E05;
  L5_OBJ_ID=E06;
  L6_OBJ_ID=E07;
  ROBJTYP=E08;
  L1_OBJ_TYPE=E09;
  L2_OBJ_TYPE=E10;
  L3_OBJ_TYPE=E11;
  L4_OBJ_TYPE=E12;
  L5_OBJ_TYPE=E13;
  L6_OBJ_TYPE=E14;
  BAN_H_ID=E15;
  BAN_H_LEVEL=E16;
  SUB_H_ID=E17;
  SUB_H_LEVEL=E18;
 COMPUTE CM__COUNTER = (CM__COUNTER+1);
 MATCH ROOT_ID 
 MATCH L1_OBJ_ID 
 MATCH L2_OBJ_ID 
 MATCH L3_OBJ_ID 
 MATCH L4_OBJ_ID 
 MATCH L5_OBJ_ID 
 MATCH L6_OBJ_ID 
    ON MATCH COMPUTE
       TRN1 = TRN1 + 1;
       DUP1 = DUP1 + 1;
    ON MATCH REJECT
    ON NOMATCH COMPUTE
       TRN1 = TRN1 + 1;
       ACC1 = ACC1 + 1;
       INP1 = INP1 + 1;
    ON NOMATCH INCLUDE
   IF (FOCERROR NE 0) GOTO ROLL;
  IF (CM__COUNTER EQ 1000) GOTO LOGCOMM;
 GOTO TOP
 ENDCASE
 
 CASE LOGCOMM
   SQL COMMIT
  COMPUTE
   CM__COM_TOT = (CM__COM_TOT+CM__COUNTER);
 TYPE 
" (ICM18745) Commit forced at: <CM__COM_TOT for <CM__COUNTER rows"
  COMPUTE
   CM__COUNTER = 0;
 GOTO TOP
 ENDCASE
 
 CASE ROLL
   SQL ROLLBACK
   GOTO EXIT
 ENDCASE
 
 CASE AT START
-DEFAULT &STARTAT = 0
-IF (&STARTAT EQ 0) GOTO :SK_START;
-TYPE  (ICM18760) Skipping &STARTAT row(s) before starting load
 START &STARTAT
-:SK_START
-DEFAULT &STOPAT  = 0
-IF (&STOPAT EQ 0) GOTO :SK_STOP;
-TYPE  (ICM18761) Stopping load after &STOPAT row(s) loaded.
 STOP &STOPAT
-:SK_STOP
  LOG TRANS MSG OFF
  LOG ACCEPT MSG OFF
  LOG DUPL MSG OFF
  LOG NOMATCH MSG OFF
  LOG INVALID MSG OFF
  LOG FORMAT MSG OFF
 CHECK 1000000000
 ENDCASE
  DATA VIA SQLGET
 END



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 20, 2008, 11:58 AM
roby
Good One
thank you!
May 21, 2008, 05:39 PM
Waz
FYI

If you change your code to a HOLD, you don't need the FIXFORM {values}.

TABLE FILE MINNIE
PRINT ONE_FIELD 
TWO_FIELD 
BY ONE_FIELD NOPRINT 
BY TWO_FIELD NOPRINT 
ON TABLE HOLDLIST PRINTONLY
ON TABLE HOLD AS KEYDEL
END 

MODIFY FILE ARXXXX 
FIXFORM FROM KEYDEL 

{Modify commands}
 
DATA ON KEYDEL 
END 



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

September 26, 2012, 03:11 PM
dojo
The following MODIFY works approximately 99.9999% of the time, BUT, occasionally the LINEITEMINS rows get dropped leading me to believe that the user is either closing their session while this is running or loses their connection or some other interuption occurs in the middle of this process resulting in a bad accounting transaction. It was my understanding that the default setting for WF was such that if the MODIFY was interrupted that the interface would issue a rollback for all previous work (BT with no ET). Is this true? Is there another setting that I need to issue in the top of the MODIFY? Thanks. I am using TeraData. joe



COMBINE VOUCHERINS LINEITEMINS MAILADDRINS PAYWAREH AS COMBXSTR
END
-RUN
MODIFY FILE COMBXSTR
SQLDBC SET LOADONLY
COMPUTE NS_DOCNO/A9=;
VO_INPUTDATETIME/A26=;
MA_INPUTDATETIME/A26=;
VO_CREATEID/A8=;
TA_TBLSEG/A8=;
TA_INVNO/A9=;
TA_ERRKEY/A4=;
EM_ERRCODE/A4=;
EM_MESSAGE/A100=;
EM_TYPE/A100=;
STATUS/A1=;
FIXFORM FROM LOADX
COMPUTE WH_TBLSEG/A8=VO_TBLSEG;
WH_APPLIC/A1='A';
WH_DOCNO/A9=VO_INVNO;
WH_SUBCERTNO/A22='&SBCNO';
WH_RPTMTH/A4=VO_RPTMONTH;
WH_STATUS/A1='K';
WH_SUBCERTID/A8=VO_SUBCERTID;
WH_SCHSET/A11=' ';
WH_VOUTYPE/A2=VO_TYPE;
WH_PAYDATE/YYMD=VO_PAYDATE;
WH_CREATE/YYMD=&YYMD;
WH_DISBMODE/A1=VO_DISBMODE;
WH_PAYEEID/A9=MA_TIN;
WH_AMOUNT/P15.2=VO_VOUCHAMT;
WH_VENDNAME/A30=MA_VENDNAME1;
WH_VENDNAME2/A30=MA_VENDNAME2;
WH_ADDRESS/A35=MA_ADDRESS1;
WH_ADDRESS2/A30=MA_ADDRESS2;
WH_CITY/A17=EDIT(MA_CITY,'9999999999999999');
WH_STATE/A2=MA_STATE;
WH_COUNTRY/A12=' ';
WH_ZIP/A9=MA_ZIP;
WH_VENDNO/A9=MA_VENDNO;
WH_CHKDESC1/A42=MA_CHKDESC1;
WH_CHKDESC2/A42=MA_CHKDESC2;
WH_CHKDESC3/A42=MA_CHKDESC3;
WH_CHKDESC4/A42=MA_CHKDESC4;
WH_CHKDESC5/A42=MA_CHKDESC5;
WH_ACCTNUM/A17=MA_ACCTNUM;
WH_ACCTTYPE/A1=MA_ACCTYPE;
WH_BANKROUTE/A9=MA_BANKROUTE;
WH_SCHEDULE/A11=VO_SCHEDULE;
VO_SCERTDATE/YYMD=&YYMD;
VO_ISTATUS/A1='W';
VO_SUBCSEG/A8='&SUBCSEG';

PERFORM MAIN1000

CASE MAIN1000
PERFORM INSVOSG
PERFORM INSVLSG
PERFORM INSMASG
IF WH_DISBMODE EQ 'V' OR 'T' THEN PERFORM INSWHSG;
ENDCASE

CASE INSVOSG
MATCH VO_TBLSEG VO_INVNO
ON NOMATCH INCLUDE
ENDCASE
CASE INSVLSG
MATCH VL_TBLSEG VL_INVNO VL_LINENO
ON NOMATCH INCLUDE
ENDCASE
CASE INSMASG
MATCH MA_TBLSEG MA_INVNO
ON NOMATCH INCLUDE
ENDCASE
CASE INSWHSG
MATCH WH_TBLSEG WH_APPLIC WH_DOCNO
ON NOMATCH INCLUDE
ENDCASE
LOG DUPL MSG OFF
LOG NOMATCH MSG OFF
DATA ON LOADX
END
-RUN


Linux/zVM/WF7.6.11