Want to remove multiple retrieval of same items

[jessicatx]

I am trying to "fix" a previously-written Crystal Rpt. (another author) We are calculating interest on late or non-payments. This report reads all the A/R Open Item Headers and the corresponding Open Item details. It also retrieves any Applications (payments) and any Disputed items against these. Most of the running totals and formulas produce good results. The problem seems to occur when there is more than one Application against an Open Item, plus a Dispute against this same item. When that happens, the Dispute total is multiplied.

We are on Oracle 8.1.7 and CR 8.0.1. Here is the SQL Query that was generated:

SELECT DISTINCT
    "AROIHDR"."TRANS_NBR", "AROIHDR"."R_STATUS", "AROIHDR"."CUSTOMER", "AROIHDR"."TRANS_DATE", "AROIHDR"."ORIG_AMT", "AROIHDR"."CUST_PO_NBR", "AROIHDR"."R_DESC", "AROIHDR"."TRANS_USER2", "AROITEMS"."TRANS_TYPE", "AROITEMS"."TRANS_NBR", "AROITEMS"."R_STATUS", "AROITEMS"."TRANS_DATE", "AROITEMS"."TRAN_AMT", "AROITEMS"."APPLD_AMT", "AROITEMS"."ORIG_AMT", "ARCUSTOMER"."CUSTOMER", "ARCUSTOMER"."MIN_CLASS", "CUSTDESC"."CUST_GROUP", "CUSTDESC"."R_NAME", "ARDISPUTE"."TRANS_NBR", "ARDISPUTE"."DISPUTE_AMT", "ARDISPUTE"."DISPUTE_DATE", "ARDISPUTE"."RESOLV_DATE", "ARAPPLIED"."APPLD_AMT", "ARAPPLIED"."DEPOSIT_DATE"
FROM
    "LAWSON"."AROIHDR" "AROIHDR",
    "LAWSON"."AROITEMS" "AROITEMS",
    "LAWSON"."ARCUSTOMER" "ARCUSTOMER",
    "LAWSON"."CUSTDESC" "CUSTDESC",
    "LAWSON"."ARDISPUTE" "ARDISPUTE",
    "LAWSON"."ARAPPLIED" "ARAPPLIED"
WHERE
    "AROIHDR"."COMPANY" = "AROITEMS"."COMPANY" AND
    "AROIHDR"."TRANS_TYPE" = "AROITEMS"."TRANS_TYPE" AND
    "AROIHDR"."TRANS_NBR" = "AROITEMS"."TRANS_NBR" AND
    "AROIHDR"."COMPANY" = "ARCUSTOMER"."COMPANY" AND
    "AROIHDR"."CUSTOMER" = "ARCUSTOMER"."CUSTOMER" AND
    "ARCUSTOMER"."CUSTOMER" = "CUSTDESC"."CUSTOMER" AND
    "AROITEMS"."COMPANY" = "ARDISPUTE"."COMPANY"(+) AND
    "AROITEMS"."CUSTOMER" = "ARDISPUTE"."CUSTOMER"(+) AND
    "AROITEMS"."TRANS_TYPE" = "ARDISPUTE"."TRANS_TYPE"(+) AND
    "AROITEMS"."TRANS_NBR" = "ARDISPUTE"."TRANS_NBR"(+) AND
    "AROITEMS"."COMPANY" = "ARAPPLIED"."COMPANY"(+) AND
    "AROITEMS"."TRANS_TYPE" = "ARAPPLIED"."TRANS_TYPE"(+) AND
    "AROITEMS"."TRANS_NBR" = "ARAPPLIED"."TRANS_NBR"(+) AND
    "AROIHDR"."R_STATUS" = 1 AND
    "CUSTDESC"."CUST_GROUP" = 'PHACG'
ORDER BY
    "ARCUSTOMER"."CUSTOMER" ASC,
    "AROIHDR"."TRANS_NBR" ASC

and here is the Select Statement -

AROIHDR.R_STATUS}=1 and
Left(TrimLeft ({AROIHDR.CUST_PO_NBR}),1) <> [&quot;S&quot;, &quot;D&quot;, &quot;N&quot;] and
Left(TrimLeft ({AROIHDR.R_DESC}),2) <> [&quot;PL&quot;, &quot;FT&quot;] and
Trim({ARCUSTOMER.MIN_CLASS}) <> [&quot;LIC&quot;, &quot;FTZ&quot;, 'INS'] and
Trim({AROIHDR.CUSTOMER}) <> ['000944000', '000946000', '000993000', '001202000',
      '004524000', '004730000', '005639000', '005704000',
      '006385000', '006686000', '007039000', '007410000',
      '009133000', '009442100', '006684000'] and
Trim({AROIHDR.CUSTOMER}) <> ['000003000', '000003100', '000006000', '000013000',
                             '000022000', '009669000', '007040000', '007037000',
                             '006384000', '000001740', '000001000', '001609000',
                             '004975000', '006710000', '009179000', '009342000',
                             '009521000', '009636800', '009871000', '009951700'] and
({?Close Date}-{AROIHDR.TRANS_DATE})>{@Grace Period} and
(trim(totext({ARAPPLIED.DEPOSIT_DATE}))='' or
({?Close Date}-{ARAPPLIED.DEPOSIT_DATE}) <= {@Grace Period}) and
{@Paid Date} > Date (2000,12,31) and
({@Paid Date}-{AROIHDR.TRANS_DATE})>{@Grace Period} and
{@Closed Credit Test}=&quot;Open&quot; and
{@Days Interest}>=3 and
{@Trim TransNbr} <> ['942694', '946831', '949931', '956363', '963268', '846520', '855215', 
                     '971258', '972298', '972299'] and
{@Trim TransNbr} <> ['983329', '001520', '005662', '006348', '013302', '013920', '972674',
                     '974073', '976300', '030057'] and
{CUSTDESC.CUST_GROUP} = &quot;PHACG&quot;

I picked up that DISTINCT hint from an earlier posting. I thought it might be my answer, but the result was the same as my previous run.

From reading other questions and replies, I have tried to include things that are asked for AFTER the initial posting. I have tried various things on this report - and although I am getting more "fluent" in CR, my problem is that I will use it intensively for a while, then have to put it down for months. Doesn't do much for your skills! I hope that someone can help me.

jessica

 

[stevefah]

Hi, Jessica---
Without going through that whole SQL statement thing, why don't you just suppress duplicates?
Do a conditional suppression on the details section using something like:
notonfirstrecord and {ARDISPUTE"."DISPUTE_AMT}= previous({ARDISPUTE"."DISPUTE_AMT})


and see if that works for you.

 

[jessicatx]

Steve -

Thanks for the idea. The problem is that the detail section is for the Open Items, and the Disputed Item is a "sometimes" thing - so that is why I have it as a left join to the Open Items.

I am getting the totals on the Disputed Items thru a Running Total, which has a formula in the Evaluation process. In this formula, I am summing if the Disputed resolution date is not null, and also bypassing a couple of other date conditions. I am doing a reset on this running total when grp-level 2 (Transaction number from the O/I Header) changes.

If I use this suppression, where would I use it?

Thanks for any help. This is a real pain of a problem, I know. It was hard for me to describe. I think I put in TOO much info, but sometimes folks want to see Select stmts, etc.

jessica