October 05, 2004, 04:58 AM
TerriHi 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?