I am trying to create a hold file and then run a match file with the hold file. Here's the code that creates the hold file:

SET ASNAMES=ON
TABLE FILE IMSUMAMH
SUM
TODAY AS 'DATE'
NUM_OVER
TOTAL_OVER_BAL
MIN.TOTAL_OVER_BAL AS 'MAX_OVER'
COMPUTE AVG_OVER/P13.2C = TOTAL_OVER_BAL / NUM_OVER;
CURBAL
COMPUTE OVER_RATIO/P8.7 = ABS(TOTAL_OVER_BAL) / CURBAL; AS 'RATIO'
NUM_OD_WV
AMT_OD_WV
NUM_NSF_WV
AMT_NSF_WV
ON TABLE HOLD AS 'IMOVERDRAFT' FORMAT FOCUS INDEX TODAY
END

The hold file correctly creates one record. I then want to add another record to this file. This is the code that I use:

TABLE FILE IMSUMAMH
SUM
TODAY AS 'DATE'
NUM_OVER
TOTAL_OVER_BAL
MIN.TOTAL_OVER_BAL AS 'MAX_OVER'
COMPUTE AVG_OVER/P13.2C = TOTAL_OVER_BAL / NUM_OVER;
CURBAL
COMPUTE OVER_RATIO/P8.7 = ABS(TOTAL_OVER_BAL) / CURBAL; AS 'RATIO'
NUM_OD_WV
AMT_OD_WV
NUM_NSF_WV
AMT_NSF_WV
ON TABLE HOLD FORMAT FOCUS INDEX TODAY
END
MATCH FILE IMOVERDRAFT
BY DATE
BY NUM_OVER
BY TOTAL_OVER_BAL
BY MAX_OVER
BY AVG_OVER
BY CURBAL
BY RATIO
BY NUM_OD_WV
BY AMT_OD_WV
BY NUM_NSF_WV
BY AMT_NSF_WV
RUN
FILE HOLD
BY DATE
BY NUM_OVER
BY TOTAL_OVER_BAL
BY MAX_OVER
BY AVG_OVER
BY CURBAL
BY RATIO
BY NUM_OD_WV
BY AMT_OD_WV
BY NUM_NSF_WV
BY AMT_NSF_WV
ON MATCH HOLD AS 'IMOVERDRAFT' OLD-OR-NEW
END

It appears to correctly create the match file but when I try to run against it I get 0 records. Any thoughts as to why?

Stephen A

Your first HOLD and last HOLD have the same name: 'IMOVERDRAFT'

I'm trying to update the same file with a second record. I want to use the same name and update the file using the OLD-OR-NEW syntax.

MODIFY/MAINTAIN updates FOCUS DB's; search on the MORE command to concatenate files.

Doesn't Match require at least one field besides all they 'by'?

Try putting in a -RUN after each END to make sure that the file actually exists before trying to execute the next section. Then as Tom says, name the last hold file something different - if nothing else for the sake of eliminating confusion.

Also - no need for the single quotes when using HOLD AS. Lastly, if you are only wanting to add a record to the first file, you can simply run the first part of the second request and then do a USE to concatenate the two tables for further requests. For FOCUS files, they do not need to physically reside in the same file.

If you are trying to ADD another record that contains the same fields, using MATCH is not the correct approach. MATCH FILE will add the second files fields to the same record giving you a longer single record.

In order to pull the same fields from different data sources to create one file with two records, use the MORE syntax.

Try this as an example:

TABLE FILE CAR
PRINT RETAIL_COST DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
WHERE CAR EQ 'PEUGEOT'
ON TABLE HOLD AS 'THESAMENAME'
END
-RUN
TABLE FILE THESAMENAME
PRINT RETAIL_COST DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
ON TABLE HOLD AS 'THESAMENAME'
MORE
FILE CAR
WHERE CAR EQ 'TRIUMPH'
END
-RUN
TABLE FILE THESAMENAME
PRINT *
END

Okay, now I'm trying to add a record to the FOCUS file IMOVERDRAFT by using the MODIFY technique. Here's the code I'm using:

JOIN
IMSUMAMH.SEG01.ACCT IN IMSUMAMH TO MULTIPLE IMSUMATH.SEG01.ACCT IN IMSUMATH
AS J0
END
DEFINE FILE IMSUMAMH
TODAY/MDYY = '05/18/2007';
YESTERDAY/MDYY = TODAY - 1;
DAYS_DIFF/I6 = DATEDIF(R_DATE_OD, TODAY, 'D');
NUM_OVER/I6 = IF R_DATE_OD NE '' THEN 1 ELSE 0;
TOTAL_OVER_BAL/P13.2C = IF R_DATE_OD NE '' THEN CURBAL ELSE 0;
NUM_OD_WV/I6 = IF TC EQ '3616' AND DTPOST EQ '05/18/2007' THEN 1 ELSE 0;
AMT_OD_WV/P13.2C = IF TC EQ '3616' THEN TRAN_AMT ELSE 0;
NUM_NSF_WV/I6 = IF TC EQ '3516' AND DTPOST EQ '05/18/2007' THEN 1 ELSE 0;
AMT_NSF_WV/P13.2C = IF TC EQ '3516' THEN TRAN_AMT ELSE 0;
END
SET ASNAMES=ON
TABLE FILE IMSUMAMH
SUM
TODAY
NUM_OVER
TOTAL_OVER_BAL
MIN.TOTAL_OVER_BAL AS 'MAX_OVER'
COMPUTE AVG_OVER/P13.2C = TOTAL_OVER_BAL / NUM_OVER;
CURBAL
COMPUTE OVER_RATIO/P8.7 = ABS(TOTAL_OVER_BAL) / CURBAL; AS 'RATIO'
NUM_OD_WV
AMT_OD_WV
NUM_NSF_WV
AMT_NSF_WV
ON TABLE HOLD FORMAT FOCUS INDEX TODAY
END
MODIFY FILE IMOVERDRAFT
FIXFORM FROM HOLD
DATA ON HOLD
END

When I try to run this program I get the following error.
0 ERROR AT OR NEAR LINE 36 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC229) ERROR READING EXTERNAL DATA FILE: HOLD

Any thoughts?

Stephen A

The logic ON MATCH/NOMATCH INCLUDE is not valid, but this is:

MODIFY FILE IMOVERDRAFT
FIXFORM FROM HOLD
MATCH TODAY
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON HOLD
END

and is the default logic created from

MODIFY FILE IMOVERDRAFT
FIXFORM FROM HOLD
DATA ON HOLD
END

The FIXFORM input is for flat fixed format external files, so as Tom says, use HOLD not HOLD FORMAT FOCUS.

That one change should be all you need.

The output ON TABLE HOLD AS IMOVERDRAFT FORMAT FOCUS INDEX TODAY from your table request will provide a single segment FOCUS file with an S1 key on TODAY. The INDEX TODAY is unnecesary, key and index are seperate entities in the FOCUS file structure.

If the value of DATE is the same for both table requests, then you will basically be providing a duplicate record key. As a matter of programming correctness it would be better to provide a non-duplicate key and use it in a BY. If the value of DATE is different then that would work also.

DEFINE FILE IMSUMAMH
FILEKEY/A1='A';
END
TABLE FILE IMSUMAMH
SUM fields
BY FILEKEY
ON TABLE HOLD AS IMOVERDRAFT FORMAT FOCUS
END
DEFINE FILE IMSUMAMH
FILEKEY/A1='B';
END
TABLE FILE IMSUMAMH
SUM fields
BY FILEKEY
ON TABLE HOLD
END
MODIFY FILE IMOVERDRAFT
FIXFORM FROM HOLD
MATCH FILEKEY
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON HOLD
END

This would provide an single segment, S1, FOCUS db keyed on the field FILEKEY.
(21st May - fixed a syntax error I noticed.)

This message has been edited. Last edited by: Alan B,

Thanks Alan, your solution helped me fix the problem.

Stephen