[SOLVED]Parameterized report with duplicate records

October 19, 2010, 04:57 PM

<d3nis370>

I have a report that gets the user to enter a date range. Once the range is entered it returns the data for that specific range. All the data is correct but for some reason I am getting dupicate records and I don't know why. Any help is appreciated

This message has been edited. Last edited by: <d3nis370>,

October 19, 2010, 05:12 PM

Waz

As you have not posted your code, we can only guess what is happening.

My initial guess would be that you have a join or you are tabling from a multi-segment file.

Posting your code will help.

October 19, 2010, 05:23 PM

<d3nis370>


JOIN
 ACCT AND LOAN_NO IN FDI001
 TO MULTIPLE ACCT AND LOAN_NO
 IN FDI002 TAG J0 AS J0
 END
DEFINE FILE FDI001
SentVIA/A3=' ';
Trk#/A3=' ';
DateofDisposition/P9=IF J0.CHECK_STATUS EQ 'P' THEN J0.CK_SENT_DT ELSE J0.PROCESS_DT;
I8YYMDFIELD/I8YYMD=DateofDisposition;
YYMDofdisposition/YYMD=I8YYMDFIELD;
END
TABLE FILE FDI001
PRINT 
     'J0.CK_RCVD_DT' AS 'Date Check ,Received'
     'BORROWER_NAME' AS 'Name'
     'LOAN_NO' AS 'Loan Number'
     'J0.CHECK_AMT' AS 'Amount'
     'J0.INS_CO' AS 'Issuer'
     'J0.FDI002.DateofDisposition'
     COMPUTE DaysOutstanding/P9 = DateofDisposition - J0.CK_RCVD_DT;
 AS 'Days Outstanding'
     SentVIA AS 'Sent VIA'
     Trk#
     'J0.CHECK_NO' AS 'Check Number'
HEADING
"Check Log (Endorse)"
FOOTING
""
WHERE ( ACCT EQ 7125 OR 7126 OR 7127 ) AND ( J0.CHECK_STATUS EQ 'E' ) AND ( J0.YYMDofdisposition GE '&FromDate' ) AND ( J0.YYMDofdisposition LE '&ToDate' );

October 19, 2010, 05:59 PM

Waz

So your report just prints the results of a join.

I would expect that there are multiple record in the FI002 table.

You can resolve this be adding BY fields and changing to a SUM, but beware that a sum will affect numeric fields, you may have to prefix them with a FST, LST or MAX, if you do not want them totaled.

October 19, 2010, 09:54 PM

Arif

As Waz asked. It will be helpful if you can tell us little about your report. You are using a one to many join.

You should look into you data if you dont have multiple records of your data, you shouldnt get multiple. If you have multiple records of your data in database, you need to use some sort of logic to get unique records. For example, SELECT DISTINCT * FROM TABLE in SQL.

October 20, 2010, 11:10 AM

<d3nis370>

This is what I changed and it got rid of the duplicate records.


JOIN
 FDI001.FDI001.FDI001_ACCT AND FDI001.FDI001.FDI001_LOAN_NO IN FDI001
 TO MULTIPLE FDI002.FDI002.FDI002_ACCT AND FDI002.FDI002.FDI002_LOAN_NO
 IN FDI002 TAG J0 AS J0
 END
DEFINE FILE FDI001
SentVIA/A3=' ';
Trk#/A3=' ';
DateofDisposition/P9=IF J0.FDI002.FDI002_CHECK_STATUS EQ 'P' THEN J0.FDI002.FDI002_CK_SENT_DT ELSE J0.FDI002.FDI002_PROCESS_DT;
I8YYMDFIELD/I8YYMD=DateofDisposition;
YYMDofdisposition/YYMD=I8YYMDFIELD;
daysoutstanding/P9=DateofDisposition - J0.FDI002.FDI002_CK_RCVD_DT;
END
TABLE FILE FDI001
SUM 
     'FST.FDI001.FDI001.FDI001_LOAN_NO' AS 'Loan Number'
     'FST.J0.FDI002.FDI002_CHECK_AMT' AS 'Amount'
     'FST.J0.FDI002.FDI002_INS_CO' AS 'Issuer'
     'FST.J0.FDI002.DateofDisposition' AS 'FST,J0.FDI002.DateofDisposition'
     SentVIA AS 'Sent VIA'
     Trk#
     'FST.J0.FDI002.daysoutstanding' AS 'FST,J0.FDI002.daysoutstanding'
BY 'J0.FDI002.FDI002_CHECK_NO'
BY 'FDI001.FDI001.FDI001_BORROWER_NAME'
BY 'J0.FDI002.FDI002_CK_RCVD_DT' AS 'Date Check ,Received'
HEADING
"Check Log (Endorse)"
FOOTING
""
WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( J0.FDI002.FDI002_CHECK_STATUS EQ 'E' ) AND ( J0.FDI002.YYMDofdisposition GE '&FromDate' ) AND ( J0.FDI002.YYMDofdisposition LE '&ToDate' ) AND ( J0.FDI002.FDI002_CHECK_NO EQ J0.FDI002.FDI002_CHECK_NO );

Thanks for the help!

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!