duplicate records

[calvinw]

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"

 

[focwizard]

Can you provide some example of WHAT is being duplicated? Is it an entire record, or just parts of it?

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?

 

[calvinw]

The entire record is being duplicated.
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.

 

[focwizard]

You're JOINing from BPDNEW to BIFIL and CRFILL08. Since both JOINs are 'unique' (you haven't secified 'All' or 'MULTIPLE' to indicate there may be multiple children), we ASSUME that you should only get AT MOST oner child iunstance from each segment for a given parent, and pass the JOIN to the engine, which would result in a CARTESIAN Product, if you had multiples in both children for a given parent.

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?

 

[calvinw]

Okay, I see what you're saying. I'll verify the datasource content and I'll find a way to determine how many records I'm actually getting from the selection criteria.
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.