Hi,

I am new to merging files. Do you have any good examples? My case is as follows:

All the files are stored in DB2/400 as relational databases
File 1 - Transportation File - Sort Fields
Company Branch Division Year File Suffix. Only one record per sort
File 2 - Container File - Sort Fields same as above. Multiple records per sort depending on the number of containers per each transportation file
File 3 - Purchase Order - Sort Field House Bill #, this file is only existing in File 1 - Multiple records per sort

Can anyone help?

Terri, can you JOIN them?
JOIN COMPANY AND BRANCH AND DIVISION AND YEAR IN FILE1
TO ALL COMPANY AND BRANCH AND DIVISION AND YEAR IN FILE2 AS J1
...then i don't know about your next file, what is the key field?
JOIN keyfield IN FILE1 TO ALL keyfield IN FILE3 AS J2

Use the fewest fields possible to define a join, and in DB2 make sure those fields are indexed (your DB2 D.B.A. can tell you or do it for you)

Hi Susannah,

Thanks for your advice. I already tried this but the results are not what I expected. Here is my code(see my comments after code"):

"SET ALL = ON
JOIN CLEAR *
JOIN POHJ02.POHFCO AND POHJ02.POHFBR AND POHJ02.POHFDV AND POHJ02.POHFYR AND POHJ02.POHFL#
AND POHJ02.POHFSF IN POHJ02 TO ALL CXL003.CXCOCD AND CXL003.CXBRCD AND CXL003.CXDVCD
AND CXL003.CXFLYY AND CXL003.CXFILE AND CXL003.CXFSFX IN CXL003 AS J0
END
JOIN POHJ02.POHCUC AND POHJ02.POHHAB IN POHJ02 TO T@TTFL04.TTCUC AND T@TTFL04.TTHBL IN T@TTFL04 AS J0
END

DEFINE FILE POHJ02
TODAY/I8MDYY=&MDYY;
DNEWDATE/MDYY=TODAY;
CURRDATE/A8MDYY=DNEWDATE;
CURRY/A4=EDIT(CURRDATE,'$$$$9999');
CURRM/A2=EDIT(CURRDATE,'99');
CURRD/A2=EDIT(CURRDATE,'$$99');
CURRDTCYM/A6=CURRY | CURRM;

LASTMTHDATE/MDYY=DATEADD (DNEWDATE, 'M', -1.999);
LASTMTHDATE1/A8MDYY=LASTMTHDATE;
LASTMT/A2=EDIT(LASTMTHDATE1, '99');
LASTMTHYR/A4=EDIT(LASTMTHDATE1, '$$$$9999');
LASTYRMTH/A6= LASTMTHYR || LASTMT;
SELDATE/A6 = IF CURRD GE '07' THEN CURRDTCYM ELSE LASTYRMTH;

TTFDT1I/I8MDYY=TTFDT1;
IDATEY/A4=EDIT(TTFDT1I,'$$$$9999');
IDATEM/A2=EDIT(TTFDT1I,'99');
IDATED/A2=EDIT(TTFDT1I,'$$99');
IDATE/A8=IDATEY | IDATEM | IDATED;
IDATEA/D8.0 = ATODBL(IDATE, '8', IDATEA);
IMPORTDATE/A10=IF TTFDT1I EQ 01011998 OR IDATE EQ '19001231' THEN ' ' ELSE IDATEM | '/' | IDATED | '/' | IDATEY;

TTCDTLI/I8MDYY=TTCDTL;
TTCDTLY/A4=EDIT(TTCDTLI,'$$$$9999');
TTCDTLM/A2=EDIT(TTCDTLI,'99');
TTCDTLD/A2=EDIT(TTCDTLI,'$$99');
EDATE/A8 = TTCDTLY | TTCDTLM | TTCDTLD;
EDATEC/HYYMDS = HINPUT(8,EDATE,8,'HYYMDS');
EXPORTDATE/A10=IF TTCDTLI EQ 01011998 OR EDATE EQ '19001231' THEN ' ' ELSE TTCDTLM | '/' | TTCDTLD | '/' | TTCDTLY;

TTDTENI/I8MDYY=TTDTEN;
TTDTENY/A4=EDIT(TTDTENI,'$$$$9999');
TTDTENM/A2=EDIT(TTDTENI,'99');
TTDTEND/A2=EDIT(TTDTENI,'$$99');
ENTDATE/A8 = TTDTENY | TTDTENM | TTDTEND;
ENTRYDATE/A10=IF TTDTENI EQ 01011998 OR ENTDATE EQ '19001231' THEN ' ' ELSE TTDTENM | '/' | TTDTEND | '/' | TTDTENY;

TTCDTDI/I8MDYY=TTCDTD;
TTCDTDY/A4=EDIT(TTCDTDI,'$$$$9999');
TTCDTDM/A2=EDIT(TTCDTDI,'99');
TTCDTDD/A2=EDIT(TTCDTDI,'$$99');
PODDATE/A8 = TTCDTDY | TTCDTDM | TTCDTDD;
DESTDATE/A10=IF TTCDTDI EQ 01011998 OR PODDATE EQ '19001231' THEN ' ' ELSE TTCDTDM | '/' | TTCDTDD | '/' | TTCDTDY;

TTDTDCI/I8MDYY=TTDTDC;
TTDTDCY/A4=EDIT(TTDTDCI,'$$$$9999');
TTDTDCM/A2=EDIT(TTDTDCI,'99');
TTDTDCD/A2=EDIT(TTDTDCI,'$$99');
CNSDATE/A8 = TTDTDCY | TTDTDCM | TTDTDCD;
CONSDATE/A10=IF TTDTDCI EQ 01011998 OR CNSDATE EQ '19001231' THEN ' ' ELSE TTDTDCM | '/' | TTDTDCD | '/' | TTDTDCY;

ESDC/A2 = FTOA(POHSCC,'(F2L)','A2');
ESDY/A2 = FTOA(POHSYY,'(F2L)','A2');
ESDM/A2 = FTOA(POHSMM,'(F2L)','A2');
ESDD/A2 = FTOA(POHSDD,'(F2L)','A2');
ESDATE/A10 = ESDM | '/' | ESDD | '/' | ESDC | ESDY;
EXPSDATE/A8 = ESDC | ESDY | ESDM | ESDD;
ESDATEC/HYYMDS = HINPUT(8,EXPSDATE,8,'HYYMDS');

EDDC/A2 = FTOA(POHECC,'(F2L)','A2');
EDDY/A2 = FTOA(POHEYY,'(F2L)','A2');
EDDM/A2 = FTOA(POHEMM,'(F2L)','A2');
EDDD/A2 = FTOA(POHEDD,'(F2L)','A2');
EDDATE/A10 = EDDM | '/' | EDDD | '/' | EDDC | EDDY;

END

TABLE FILE POHJ02

-*HEADING
-*"DAILY IMPORT ACTIVITY REPORT FOR CUSTOMER <POHCUN>"
PRINT
TTHBL AS 'House Bill #'
ESDATE AS 'Expected Ship Date'
EXPORTDATE AS 'Export Date'
COMPUTE CNTR#/A12 = CXCACD | ' ' | CXCNCD; AS 'Cntr#'
POHSCNM AS 'Shipper Name'
POHPO# AS 'Customer PO #'
PODPT# AS 'Part Number'
PODPND AS 'Commodity Description'
PODQTY AS 'Quantity'
IMPORTDATE AS 'Import Date'
TTPOD AS 'Destination'
DESTDATE AS 'ETA Dest Date'
EDDATE AS 'Delivery Requested Date'
CONSDATE AS 'Del to Cnee Date'
COMPUTE ESDVAR/I4 = MDY(ESDATEC, EDATEC); AS 'Expected Ship Date to Export Date'
COMPUTE TRXPOEVAR/I4 = MDY(TTCDTLI, TTFDT1I); AS 'Transit Time to POE'
COMPUTE TRXDSTVAR/I4 = MDY(TTCDTLI, TTCDTDI); AS 'Transit Time to ETA Dest Date'
COMPUTE TRXCNSVAR/I4 = MDY(TTCDTLI, TTDTDCI); AS 'Transit Time to Cnee Date'
BY ESDATE NOPRINT
BY POHHAB NOPRINT
WHERE (POHCUC EQ '&CONS' OR POHCUF EQ '&CONS') AND ((POHHAB EQ ' ') OR (TTRCT EQ 'OIFI' OR 'AIFI') AND (IDATE GE '200409'));
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE-NUM OFF"

The problem is that the records multiply because of the following:

1) POHJ02 has multiple records for the index.
2) CXL003 has multiple record for the index.
3) T@TTFL04 only has one record per index, but I can not use this as my main file because I need to get records from POHJ02 that have a null POHHAB or a value in POHHAB that meets the selection criteria. Also, the report must be sorted by a field in the main file (POHJ02) and WEBFOCUS expects me to use only fields from the main file for my sort.
3) Example result as below:
House Bill # Expected Ship Date Export Date Cntr# Shipper Name Customer PO # Part Number Commodity Description Quantity

S0409024RZS 09/25/2004 09/25/2004 CCLU 6447960 LERADO CHINA LIMITED 165940 4470702 6
S0409024RZS 09/25/2004 09/25/2004 CCLU 6497766 LERADO CHINA LIMITED 165940 4470702 6
S0409024RZS 09/25/2004 09/25/2004 TGHU 8371047 LERADO CHINA LIMITED 165940 4470702 6
S0409024RZS 09/25/2004 09/25/2004 CCLU 6447960 LERADO CHINA LIMITED 165931 4470702 6
S0409024RZS 09/25/2004 09/25/2004 CCLU 6497766 LERADO CHINA LIMITED 165931 4470702 6
S0409024RZS 09/25/2004 09/25/2004 TGHU 8371047 LERADO CHINA LIMITED 165931 4470702 6
S0409024RZS 09/25/2004 09/25/2004 CCLU 6447960 LERADO CHINA LIMITED 165941 4470702 6
S0409024RZS 09/25/2004 09/25/2004 CCLU 6497766 LERADO CHINA LIMITED 165941 4470702 6
S0409024RZS 09/25/2004 09/25/2004 TGHU 8371047 LERADO CHINA LIMITED 165941 4470702 6
S04090598NJ 09/25/2004 09/29/2004 CBHU 0605185 HEFEI ELSIN HOUSEWARES 167387 4581012 LAUNDRY PRODUCTS 1500
S04090598NJ 09/25/2004 09/29/2004 CBHU 1796701 HEFEI ELSIN HOUSEWARES 167387 4581012 LAUNDRY PRODUCTS 1500
S04090598NJ 09/25/2004 09/29/2004 CBHU 0605185 HEFEI ELSIN HOUSEWARES 168018 1030010 LAUNDRY PRODUCTS 17766
S04090598NJ 09/25/2004 09/29/2004 CBHU 1796701 HEFEI ELSIN HOUSEWARES 168018 1030010 LAUNDRY PRODUCTS 17766

So, for S04090598NJ I should only get two records in my result, one for CNTR CBHU 0605185 PO # 168018 PT# 1030010 (qty = 17776) and one for CNTR CBHU 1796701 PO #167387 PT # 4581012 (qty = 1500)

What am I doing wrong?

Terri, I looked out over your request. There are two possibilities that come to mind, .
1) Is there another matching key between pohj02 and cxl003, specifically, CXCNCD . If so, then add that to the JOIN. Then, you will only get one match.

If that is not possible, then how about changing your PRINT to a SUM, and adding BY CXCACD NOPRINT BY cxcncd NOPRINT (You might also need to change the PODQTY to AVE.PODQTY so that you don't get jthe multiplicative effect.

If neither one of those will do it,open a case with Customer Support -- either through techsupport CASE MANAGEMENT, or by calling us at 1-800-736-6130 . I suspect that we would need the masters for the two major files.

Hi Noreen, Unfortunately there are no other fields from the main file with CXL003 to match, so I will try your second proposal and will let you know the results.

Thanks for your input!