So, I'm doing a MATCH FILE command. Looks more or less like this:

MATCH FILE ACS_SELREC_OTHINS_HOLD1
PRINT MAP_ID
NAME_KEY
APNAME_CHANGE
ADDRESS_KEY
BY APP_KEY
RUN
FILE ACS_SELREC_OTHINS_HOLD4
PRINT OTHER_RX_OPID
OPID
BY APP_KEY
AFTER MATCH HOLD AS ACS_SELREC_OTHINS_HOLD5 OLD
END
-RUN

Here's the thing -- I have two records in my "new" file for one record in my "old" file. The result I get shows a fully formed first record, with all the fields from the old file and all the fields from the new file. The second record has no fields from the old file and all the fields from the new file.

I need all the fields from the old file in both output records.

I may replace the whole thing with a join, but the question is this -- is this normal behavior for a MATCH FILE command? I have dozens of these in a very big unit that I did not write, but that I'm on the hook for to correct due to an error. Are there options on MATCH FILE that tell it to include all the first file's fields in each of the matches it finds?

J.

This message has been edited. Last edited by: <Kathryn Henning>,

If I understand well, your "old" file is ..._HOLD1 and "new" is ..._HOLD4

If you have more records in HOLD4 then HOLD1, only first match between HOLD1 and HOLD4 will be "joined" with data of both files.
If you want all records from HOLD4 inherit from HOLD1 values you need a JOIN or maybe trying inverting your MATCH FILE may do the job:

MATCH FILE ACS_SELREC_OTHINS_HOLD4
PRINT OTHER_RX_OPID
 OPID
BY APP_KEY
RUN
FILE ACS_SELREC_OTHINS_HOLD1
PRINT MAP_ID 
 NAME_KEY 
 APNAME_CHANGE
 ADDRESS_KEY
BY APP_KEY
AFTER MATCH HOLD AS ACS_SELREC_OTHINS_HOLD5 OLD
END
-RUN

Elementary:

In match file, there is a major difference between SUM and PRINT. Where a non-aggregating verb (PRINT) is used, there can be multiple instances per combination of sort-key values, and the order of occurrence within each unique set of BY values is added as an implicit lowest=level sort key to break any ties. If the OLD and NEW sets have different cardinality for a given key, the excess instances on the one side get paired with null instances on the other. Hence you are getting
#1 :: #1
#2 :: (nada)
as the two merged records, when OLD contains two instances and NEW just one for a given sort-key.

You need to re-work your request to use SUM / SUM, rather than PRINT / PRINT. You'll need to add at least one additional sort key, such as will suffice to give each retrieved record instance a unique place in the sort order (a unique row in the respective internal matrix before the two are merged); then SUM will not encounter any key duplication, so the nominal aggregation will have no opportunity to combine multiple records, and you'll get the same result as you'd expect for PRINT.

Thank you both. I think a Join is in order. Shouldn't be a problem.

quote:

Elementary:

And then Professor Gross proceeds with his dissertation !

In addition to wisdom from Prof. Gross, I would advise trapping the SQL (if applicable) from both the MATCH version and the JOIN version and comparing. It is likely that the JOIN version will be more efficient as the JOIN will be actioned at the RDBMS whereas the MATCH is likely to result in separate SELECT statements for each table and the work done by WebFOCUS.

T