My requirement is to update the focus database but before updating the D/b, I need to check wheather the input file is a duploicate file or processed already by checking the input file header with previously processed file header.
It has a unique field named RECID#.
I should not miss the file or duplicate.
Any suggestions/help..
Thanks in advance...This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.13
Mainframe all output (Report in mainframe)
June 13, 2011, 06:26 PM
Waz
So you need to check to see if the header is in another input file ?
Is the headers recorded anywhere for each load ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
June 13, 2011, 07:36 PM
Poryes
Yes Waz, I need to check the current file header record with previouly processed file's header record before updating the focus d/b.
If its processed already it shouldn't update the focus d/b but it has to send a warning message.This message has been edited. Last edited by: Poryes,
WebFOCUS 7.6.13
Mainframe all output (Report in mainframe)
June 13, 2011, 11:52 PM
Waz
Do you record previous headers from load files, or have access to the previous load files ?
You could query the record, or check each load file header.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
June 14, 2011, 11:47 AM
Poryes
Yes It copies the header record onto a table and then compares the header to a table.
Any examples..
WebFOCUS 7.6.13
Mainframe all output (Report in mainframe)
June 14, 2011, 05:50 PM
Waz
Then why can't you just pull the header record, then filter this value against your previou headers.
Then if you get a record, i.e. &LINES, branch to a message to indicate the file is already loaded.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
June 15, 2011, 08:26 AM
ABT
Maybe I'm missing something, but why not use MATCH?
MODIFY FILE YOUR_REMOTE_TABLE
FIXFORM FROM MY_HOLD_FILE
MATCH YOUR_PRIMARY_KEY
ON MATCH REJECT
ON NOMATCH INCLUDE
DATA ON MY_HOLD_FILE
END
-RUN
You said there is a unique field RECID. Does that value come from the input file?
If so check the input file value against the database and if it exists send an error message and quit.
-READ input_file &INRECID.A?
TABLE FILE DB PRINT RECID WHERE RECID EQ &INRECID END -RUN -IF &LINES EQ 0 THEN GOTO LOADIT; -IF &LINES NE 0 THEN GOTO WARNEM;
Signature changes from project to project
June 16, 2011, 11:44 AM
Poryes
Thanks Sandy/ABT/Waz.
RECID has a serial numbers which is incremented by 1. Example, current run RECID is 11111 and the next run it should be 11112.
And also I need to check if there is a input file missing without loading into focus d/b (by checking the serial numbers).
If there is a miss in the i/p file then it should only send warning messages but load the file...
Would highly appreciate if anyone provides a sample code..This message has been edited. Last edited by: Poryes,
WebFOCUS 7.6.13
Mainframe all output (Report in mainframe)
June 20, 2011, 11:28 AM
Poryes
Hi All -
Can anyone pls tell me how to accomplish this..
Thanks!!!
WebFOCUS 7.6.13
Mainframe all output (Report in mainframe)
June 21, 2011, 11:59 AM
Francis Mariani
Why can't you build on what Sandy suggested?
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
June 21, 2011, 04:28 PM
Dan Satchell
Building on Sandy's suggestion...
If your input file has a synonym/master, you can TABLE it to get the first RECID. Otherwise, you will have to -READ it, as Sandy suggested. If the input file is not sorted by lowest RECID first, then use the MIN. prefix operator instead of FST. to get the lowest RECID from the input file. This approach assumes you will never have an input file where some of the records have already been loaded and some have not.
TABLE FILE INPUT_FILE
SUM FST.RECID
ON TABLE SAVE AS RECID1
END
-*
-RUN
-IF (&LINES GT 0) GOTO ID_CHECK1 ;
-TYPE ERROR: Input file is empty.
-TYPE ERROR: Load terminated.
-EXIT
-*
-ID_CHECK1
-READ RECID1, &RECID_1
-*
TABLE FILE DB_FILE
PRINT RECID
WHERE RECID EQ &RECID_1 ;
ON TABLE SAVE
END
-*
-RUN
-IF (&LINES EQ 0) GOTO ID_CHECK2 ;
-TYPE ERROR: Input file has already been loaded.
-TYPE ERROR: Load terminated.
-EXIT
-*
-ID_CHECK2
-SET &RECID_0 = &RECID_1 - 1 ;
-*
TABLE FILE DB_FILE
PRINT RECID
WHERE RECID EQ &RECID_0 ;
ON TABLE SAVE
END
-*
-RUN
-IF (&LINES GT 0) GOTO DB_LOAD ;
-TYPE WARNING: Input file RECIDs are out of sequence.
-TYPE WARNING: Load will continue.
-*
-DB_LOAD
MODIFY FILE DB_FILE
.
.
.
.
WebFOCUS 7.7.05
June 22, 2011, 04:58 AM
Rajna Nannat
Please find a sample code below:
TABLE FILE CAR
PRINT
CAR
COUNTRY
MODEL
-*Creating a serial counter manually
COMPUTE RECID/I9=IF RECID EQ 2 THEN 4 ELSE IF RECID EQ 7 THEN 9 ELSE LAST RECID+1;
-****Checking if records are missing****
COMPUTE MISSED_RECORD/A2=IF (RECID-LAST RECID) GT 1 THEN 'Y' ELSE 'N';
ON TABLE HOLD AS TABLE_MISSED
END
-*If MISSED_RECORD equals Y then insert record whose record id is (RECID-1)
TABLE FILE TABLE_MISSED
PRINT
COMPUTE INSERT_REC/I9=RECID-1;
BY TOTAL INSERT_REC
WHERE MISSED_RECORD EQ 'Y';
ON TABLE SAVE AS CHECK_MISSED
END
-RUN
-*Append those records with record id IN FILE CHECK_MISSED
