×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

duplicate records

thread243-1499699

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"
 

RE: duplicate records

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?

RE: duplicate records

(OP)
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.
 

RE: duplicate records

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?

RE: duplicate records

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close