duplicate records
duplicate records
(OP)
New to webfocus. Using DB2 adapter for IBM 550.
Following code produces duplicates with some date ranges.
Suggestions?
JOIN
BPDCLB AND BPDMBR IN BPDNEW TO BIFCLB AND BIFMBR IN BIFIL AS J0
END
JOIN BPDCLB AND BPDMBR IN BPDNEW TO CRCLB AND CRMBR
IN CRFILL08 AS J1
END
DEFINE FILE BPDNEW
MONTH/Mtr =BILLDATE;
YEAR/YY =BILLDATE;
ACCTS/I5CS =1;
ACCTNO/A10 =EDIT(BPDCLB)|'-'|EDIT(BPDMBR);
HFONE/A14 =IF BPDHPN EQ ' ' THEN ' '
ELSE IF BPDHPP EQ ' ' THEN ' '
ELSE IF BPDHPA EQ ' ' THEN BPDHPP|EDIT(BPDHPN,'-9999')
ELSE EDIT(BPDHPA,'(999) ')|BPDHPP|EDIT(BPDHPN,'-9999');
WFONE/A14 =IF BPDWPN EQ ' ' THEN ' '
ELSE IF BPDWPP EQ ' ' THEN ' '
ELSE IF BPDWPA EQ ' ' THEN BPDWPP|EDIT(BPDWPN,'-9999')
ELSE EDIT(BPDWPA,'(999) ')|BPDWPP|EDIT(BPDWPN,'-9999');
NAME/A35 =LCWORD(15,BPDFNM,'A15')||(' '|LCWORD(19,BPDLNM,'A19'));
NAME1/A35 =LCWORD(15,BIFFNM,'A15')||(' '|LCWORD(19,BIFLNM,'A19'));
NME/A35 =IF BIFFNM NE ' ' THEN NAME1 ELSE NAME;
LSTNME/A19 =IF BIFLNM NE ' ' THEN BIFLNM ELSE BPDLNM;
FSTNME/A15 =IF BIFFNM NE ' ' THEN BIFFNM ELSE BPDFNM; SORTNAME/A35=BPDLNM||BPDFNM;
RFCAMT/A11 =EDIT(FTOA(BPDRFCP,'(D11.2L)','A13'),'99$999$999999');
COLAMT/A11 =EDIT(FTOA(BPDCOLPMT,'(D11.2L)','A13'),'99$999$999999');
SORTFLD/A35 =&SORTFLD;
CHKDATE/YMD =BPDSTD;
MYDATE/YMD =DATECVT(CRDTE,'I6MDY','YMD');
SCADATE/MDY =MYDATE;
MYMONTH/Mtr =CHKDATE;
-INCLUDE standard_defines
END
TABLE FILE BPDNEW
HEADING
"Club <BPDCLB <CLUBNM"
&LOGOHDR
"Memberships processed and forwarded to collection agency"
"<CYCLDSC <MYMONTH <YEAR"
&OPTHDR
"Forward to Collection Agency charges will not reflect until next billing cycle"
PRINT
ACCTNO AS '&AS1'
NME AS 'Name'
HFONE AS 'Home Phone'
WFONE AS 'Work Phone'
BPDTYP AS 'T'
BPDMDE AS 'M'
BPDFRQ AS 'F'
BPDMTC AS '&AS3'
BPDSTC AS 'STC'
BPDSTD AS 'STC Date'
BPDAGE AS 'Age'
BPDPDD AS 'Next Due'
BPDDUE AS '&AS4'
BPDRFCP AS '&AS5'
BPDCOLPMT AS '&AS6'
BPDCOLDTE AS 'Collected Date'
BPDLDA AS 'Past Due'
BPDCBA AS 'Balance'
CRCDE AS 'Code'
SCADATE AS 'SCA Date'
ACCTS NOPRINT
&SORTBY NOPRINT
WHERE CHKDATE GE &RDATE AND CHKDATE LE &TODATE;
WHERE BPDCLB EQ &CLUBID; WHERE BPDCOL EQ 'Y';
WHERE BPDPCH EQ ' ';
WHERE BPDSTC EQ 'RFC';
WHERE CRCDE EQ 'SCA';
WHERE MYDATE GE CHKDATE AND DATEDIF(MYDATE,CHKDATE,'D') LE 10;
ON TABLE SUBFOOT "Totals<TOT.ACCTS<TOT.BPDRFCP<TOT.BPDCOLPMT<TOT.BPDLDA<TOT.BPDCBA"
Following code produces duplicates with some date ranges.
Suggestions?
JOIN
BPDCLB AND BPDMBR IN BPDNEW TO BIFCLB AND BIFMBR IN BIFIL AS J0
END
JOIN BPDCLB AND BPDMBR IN BPDNEW TO CRCLB AND CRMBR
IN CRFILL08 AS J1
END
DEFINE FILE BPDNEW
MONTH/Mtr =BILLDATE;
YEAR/YY =BILLDATE;
ACCTS/I5CS =1;
ACCTNO/A10 =EDIT(BPDCLB)|'-'|EDIT(BPDMBR);
HFONE/A14 =IF BPDHPN EQ ' ' THEN ' '
ELSE IF BPDHPP EQ ' ' THEN ' '
ELSE IF BPDHPA EQ ' ' THEN BPDHPP|EDIT(BPDHPN,'-9999')
ELSE EDIT(BPDHPA,'(999) ')|BPDHPP|EDIT(BPDHPN,'-9999');
WFONE/A14 =IF BPDWPN EQ ' ' THEN ' '
ELSE IF BPDWPP EQ ' ' THEN ' '
ELSE IF BPDWPA EQ ' ' THEN BPDWPP|EDIT(BPDWPN,'-9999')
ELSE EDIT(BPDWPA,'(999) ')|BPDWPP|EDIT(BPDWPN,'-9999');
NAME/A35 =LCWORD(15,BPDFNM,'A15')||(' '|LCWORD(19,BPDLNM,'A19'));
NAME1/A35 =LCWORD(15,BIFFNM,'A15')||(' '|LCWORD(19,BIFLNM,'A19'));
NME/A35 =IF BIFFNM NE ' ' THEN NAME1 ELSE NAME;
LSTNME/A19 =IF BIFLNM NE ' ' THEN BIFLNM ELSE BPDLNM;
FSTNME/A15 =IF BIFFNM NE ' ' THEN BIFFNM ELSE BPDFNM; SORTNAME/A35=BPDLNM||BPDFNM;
RFCAMT/A11 =EDIT(FTOA(BPDRFCP,'(D11.2L)','A13'),'99$999$999999');
COLAMT/A11 =EDIT(FTOA(BPDCOLPMT,'(D11.2L)','A13'),'99$999$999999');
SORTFLD/A35 =&SORTFLD;
CHKDATE/YMD =BPDSTD;
MYDATE/YMD =DATECVT(CRDTE,'I6MDY','YMD');
SCADATE/MDY =MYDATE;
MYMONTH/Mtr =CHKDATE;
-INCLUDE standard_defines
END
TABLE FILE BPDNEW
HEADING
"Club <BPDCLB <CLUBNM"
&LOGOHDR
"Memberships processed and forwarded to collection agency"
"<CYCLDSC <MYMONTH <YEAR"
&OPTHDR
"Forward to Collection Agency charges will not reflect until next billing cycle"
ACCTNO AS '&AS1'
NME AS 'Name'
HFONE AS 'Home Phone'
WFONE AS 'Work Phone'
BPDTYP AS 'T'
BPDMDE AS 'M'
BPDFRQ AS 'F'
BPDMTC AS '&AS3'
BPDSTC AS 'STC'
BPDSTD AS 'STC Date'
BPDAGE AS 'Age'
BPDPDD AS 'Next Due'
BPDDUE AS '&AS4'
BPDRFCP AS '&AS5'
BPDCOLPMT AS '&AS6'
BPDCOLDTE AS 'Collected Date'
BPDLDA AS 'Past Due'
BPDCBA AS 'Balance'
CRCDE AS 'Code'
SCADATE AS 'SCA Date'
ACCTS NOPRINT
&SORTBY NOPRINT
WHERE CHKDATE GE &RDATE AND CHKDATE LE &TODATE;
WHERE BPDCLB EQ &CLUBID; WHERE BPDCOL EQ 'Y';
WHERE BPDPCH EQ ' ';
WHERE BPDSTC EQ 'RFC';
WHERE CRCDE EQ 'SCA';
WHERE MYDATE GE CHKDATE AND DATEDIF(MYDATE,CHKDATE,'D') LE 10;
ON TABLE SUBFOOT "Totals<TOT.ACCTS<TOT.BPDRFCP<TOT.BPDCOLPMT<TOT.BPDLDA<TOT.BPDCBA"
RE: duplicate records
Your request does no sorting or aggregation, so you're just getting the detail records from the retrieval.
Also, you're JOINing from the parent table to TWO different child tables. What if a given parent instance has MULTIPLE children in each child? WHAT do you expect to see? What do you WANT to see?
RE: duplicate records
The parent instance does have multiple children in one child. I expect to see one combined record, the join matches the 'range' of records and the where clause selecting the dates should extract one record from the parent and one record from the child. I've tried various sorts that have no effect or the application fails.
RE: duplicate records
With that said, are you SURE your selection only extracts ONE record from each child for a given parent? If you use the selection on each child separately, does it give the correct reults?
RE: duplicate records
If I'm getting a cartesian product then I'll have to determine how to resolve that.
Thanks focwizard, I just needed a bump in the right direction.