Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

duplicate records

Status
Not open for further replies.
calvinw

calvinw

Programmer
Joined
Aug 20, 2008
Messages
6
Location
US
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"
 
Sort by date Sort by votes
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tracisell
  • Locked
  • Question Question
Make duplicate row records into columns
Replies
1
Views
374
FrankExp
FrankExp
jlg5454
  • Locked
  • Question Question
Update Records in Database
Replies
1
Views
259
bcbasslet
bcbasslet
jlg5454
  • Locked
  • Question Question
Update Records in focus table
Replies
1
Views
324
focwizard
focwizard
jlg5454
  • Locked
  • Question Question
Delete records off a FOC database
Replies
1
Views
309
focwizard
focwizard
sagobogger
  • Locked
  • Question Question
Find overlapping time periods between records?
Replies
1
Views
227
craigiep
craigiep

Part and Inventory Search

Sponsor

Back
Top