Hello all,

I am currently re-writing a report and am trying to slim down the code a bit. Currently the report run two querries and then matches them together in a single file, like this:

DEFINE FILE VW_FCB_RECVRY_TRK
BLANK/A45 = ' 6b) FIELD COMPLIANCE POST LEGACY NOTICE';
END
TABLE FILE VW_FCB_RECVRY_TRK
SUM
COMPUTE MONEY1/D15.2C!D = MONEY; AS ''
BY PERIOD
BY BLANK
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'L';
ON TABLE HOLD AS FCBPOST
END
DEFINE FILE VW_FCB_RECVRY_TRK
BLANK/A45 = ' 6a) FIELD COMPLIANCE PRE LEGACY NOTICE';
END
TABLE FILE VW_FCB_RECVRY_TRK
SUM
COMPUTE MONEY2/D15.2C!D = MONEY; AS ''
BY PERIOD
BY BLANK
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'A';
ON TABLE HOLD AS FCBPRE
END
MATCH FILE FCBPOST
SUM MONEY1
BY PERIOD
BY BLANK
RUN
FILE FCBPRE
SUM MONEY2
BY PERIOD
BY BLANK
AFTER MATCH HOLD AS FCB0 OLD-OR-NEW
END
TABLE FILE FCB0
SUM
COMPUTE MONEY3/D15.2C!D = MONEY1 + MONEY2; AS ''
BY PERIOD
BY BLANK
ON TABLE HOLD AS FCB1
END


My question is, can I run more than two querries, then match them all together in a single statement, similar to this:

ATCH FILE PT_ONLY_PRE_DISC
SUM MONEY50
BY PERIOD
RUN
FILE BT_ONLY_PRE_DISC
SUM MONEY51
BY PERIOD
RUN
FILE BT_ONLY_NCAMS_PRE_DISC
SUM MONEY52
BY PERIOD
RUN
FILE NEX_ONLY_PREAGE_SPRDSHT
SUM MONEY53
BY PERIOD
AFTER MATCH HOLD AS ALL_ONLY_PRE_NEXDISC OLD-OR-NEW
END

Thanks,

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

Yes, you can do more than two match queries. Here is the code snippet:

  
MATCH FILE PT_ONLY_PRE_DISC
SUM MONEY50
BY PERIOD
RUN
FILE BT_ONLY_PRE_DISC
SUM MONEY51
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE BT_ONLY_NCAMS_PRE_DISC
SUM MONEY52
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE NEX_ONLY_PREAGE_SPRDSHT
SUM MONEY53
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
END

Then do TABLE FILE HOLD and PRINT all your fields.

Regards,
Venkat

You can do that, but I do not understand why you do this.
The match is a way to combine data from different databases .
Your data coms from one database so it can be retrieved in obe statement

TABLE FILE XXXX
SUM MONEY
BY PERIOD
BY MONEY_CREDIT
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'L';
ON TABLE HOLD AS FCBPOST
END
DEFINE FILE FCBPOST
BLANK/A45 = DECODE MONEY_CREDIT(C ' 6a) FIELD COMPLIANCE PRE LEGACY NOTICE' 
L  ' 6b) FIELD COMPLIANCE POST LEGACY NOTICE');
END
TABLE FILE FCBPOST 
SUM MONEY
BY PERIOD
BY BLANK
END

I hope this gives you an idea
The computes and defines will be done at the end after collecting all the data.

Also keep in mind that MATCH expects 1 to 0-1 relations between the files.

If you have multiple right-hand-side matches for a single left-hand-side record, you will only get the first right-hand-side match in your results.

I find that rather peculiar behaviour, but that's the way it was designed.

This is my reference in regards to using MATCH.

http://ecl.informationbuilders...ource%2Ftopic147.htm

You can match multiple files together. You could have one hold file at the end or intermediate hold files after each file matched with different merge phrases. I also like to note how match is different based on the interesting use of PRINT/SUM.