Duplicate Records

lorit
365 pts.
Tags:
AS/400 Query
Duplicate records
Help - My query is returning duplicate records.  How do I eliminate that?  In addition, how can I total the adjustment amount and report one record?  Here's the query:

SELECT Distinct "T16.GNAM".N_NAME, "T16.GNAM".N_CITY, "T16.CADJ".AD_TOTAL_ADJUSTMENT, "T16.CRDC".RD_COMMENT    FROM QS36F."T16.CADJ" AS "T16.CADJ", QS36F."T16.CRDC" AS "T16.CRDC", QS36F."T16.CPRP" AS "T16.CPRP", QS36F."T16.GNAM" AS "T16.GNAM"    WHERE "T16.CADJ".AD_TAXBILL_NO = "T16.CRDC".RD_TAXBILL_NO AND "T16.CRDC".RD_TAXBILL_NO = "T16.CPRP".CP_TAXBILL_NO AND "T16.CPRP".CP_PRIMARY_TAXPAYER = "T16.GNAM".N_ID_NR AND "T16.CADJ".AD_ADJUSTMENT_DATE > 20091106 AND "T16.CADJ".AD_ADJUSTMENT_DATE < 20091205 AND "T16.CADJ".AD_TOTAL_ADJUSTMENT > -100.00 and "T16.CADJ".AD_REASON IN ('C ', 'R ')    order BY "T16.GNAM".N_NAME;

Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

With the word distinct the actual 4 fields returned cannot be duplicated. I would susect that there might be multiple rows of comments Please show us sample results and dscribed how these look like duplicates to you.

Do you want a sum of all rows returned or a sum for each subgroup (based on which fields?
Phil

Please add the responses to this page.

Phil, I ran a query for one bill on each of the files used in the query. I ran the main query on the same bill. Below are the results:

CADJ File (Amount Field) For this Bill 209004
209004 R -1586.88
209004 R -10.00
209004 C 831.17
209004 R -831.17

CRDC File (Comment Field) For this Bill 209004
SPLIT TO CONDOS PER SURVEY AG 209004 1586.88 0.00
SPLIT TO CONDOS PER SURVEY 209004 0.00 0.00
SPLIT TO CONDOS PER SURVEY 209004 0.00 831.17
PUT ON WRONG PARCEL 209004 831.17 0.00

CPRP File (Taxpayer ID)
209004 391514

GNAM File (Taxpayer Name)

391514 SLEEPY HOLLOW

Current Query Results using this one bill number:

SLEEPY HOLLOW GREEN -10.00 SPLIT TO CONDOS PER SURVEY AG
SLEEPY HOLLOW GREEN -10.00 SPLIT TO CONDOS PER SURVEY SLEEPY HOLLOW GREEN -10.00 PUT ON WRONG PARCEL SLEEPY HOLLOW GREEN 831.17 SPLIT TO CONDOS PER SURVEY AG SLEEPY HOLLOW GREEN 831.17 SPLIT TO CONDOS PER SURVEY
SLEEPY HOLLOW GREEN 831.17 PUT ON WRONG PARCEL

Desired Result (Total that amount field in the CADJ File)
SLEEPY HOLLOW GREEN 1596.88 SPLIT CONDOS PER SURVEY (GRAB THE LAST COMMENT ENTERED?)

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta 
    SELECT Distinct
   "T16.GNAM".N_NAME,
   "T16.GNAM".N_CITY,
   "T16.CADJ".AD_TOTAL_ADJUSTMENT,
   "T16.CRDC".RD_COMMENT

  FROM

     QS36F."T16.CPRP" AS "T16.CPRP",
     QS36F."T16.GNAM" AS "T16.GNAM",
     QS36F."T16.CADJ" AS "T16.CADJ",
     QS36F."T16.CRDC" AS "T16.CRDC"

  WHERE

     "T16.CPRP".CP_PRIMARY_TAXPAYER = "T16.GNAM".N_ID_NR AND
     "T16.CPRP".CP_TAXBILL_NO = "T16.CADJ".AD_TAXBILL_NO AND
     "T16.CADJ".AD_TAXBILL_NO = "T16.CRDC".RD_TAXBILL_NO AND
     "T16.CADJ".AD_ADJUSTMENT_DATE > 20091106 AND
     "T16.CADJ".AD_ADJUSTMENT_DATE < 20091205 AND
     "T16.CADJ".AD_TOTAL_ADJUSTMENT > -100.00 and
     "T16.CADJ".AD_REASON IN ('C ', 'R ')

  order BY "T16.GNAM".N_NAME
    Phil is probably on the right track. I poked around with the query to see if it could be made to read easier and perhaps make a little more sense. (And I see TAXBILL_NO provides the missing relationship from the previous question.) You might consider re-ordering your query components closer to how I put them here. (I'd be a little concerned about ordering purely by N_NAME since I can guarantee that two taxpayers at the same mailing street address can have the same name. It has happened to me personally with my father.) As part of the re-ordering, I have T16.CRDC as the last file in the chain. That's just from coming to a similar conclusion to Phil's. The query appears to be from a SQL client, possibly iSeries Navigator 'Run SQL Scripts'. If you are intending to print a report, you might use this as the basis for a QM query. It's been a while, but I think the QM form could be adapted to print only the first line of the "unique" values from the first three files while printing each comment on subsequent lines. It would at least make the 'duplicates' read sensibly. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    SELECT "T16.GNAM".N_NAME, "T16.GNAM".N_CITY, sum("T16.CADJ".AD_TOTAL_ADJUSTMENT) FROM QS36F."T16.CADJ" AS "T16.CADJ", QS36F."T16.CRDC" AS "T16.CRDC", QS36F."T16.CPRP" AS "T16.CPRP", QS36F."T16.GNAM" AS "T16.GNAM" WHERE "T16.CADJ".AD_TAXBILL_NO = "T16.CPRP".CP_TAXBILL_NO AND "T16.CPRP".CP_PRIMARY_TAXPAYER = "T16.GNAM".N_ID_NR AND "T16.CADJ".AD_ADJUSTMENT_DATE > 20091106 AND "T16.CADJ".AD_ADJUSTMENT_DATE < 20091205 AND "T16.CADJ".AD_TOTAL_ADJUSTMENT > -100.00 and "T16.CADJ".AD_REASON IN ('C ', 'R ') Group by "T16.GNAM".N_NAME, "T16.GNAM".N_CITY; This should give you the totals by person provided every name, city is unique - not likely. Phil
    54,090 pointsBadges:
    report
  • philpl1jb
    SELECT “T16.GNAM”.N_NAME, “T16.GNAM”.N_CITY, sum(”T16.CADJ”.AD_TOTAL_ADJUSTMENT) FROM QS36F.”T16.CPRP” AS “T16.CPRP”, QS36F.”T16.GNAM” AS “T16.GNAM”, QS36F.”T16.CADJ” AS “T16.CADJ”, QS36F.”T16.CRDC” AS “T16.CRDC” WHERE “T16.CADJ”.AD_TAXBILL_NO = “T16.CPRP”.CP_TAXBILL_NO AND “T16.CPRP”.CP_PRIMARY_TAXPAYER = “T16.GNAM”.N_ID_NR AND “T16.CADJ”.AD_ADJUSTMENT_DATE > 20091106 AND “T16.CADJ”.AD_ADJUSTMENT_DATE < 20091205 AND “T16.CADJ”.AD_TOTAL_ADJUSTMENT > -100.00 and “T16.CADJ”.AD_REASON IN (’C ‘, ‘R ‘) Group by “T16.GNAM”.N_NAME, “T16.GNAM”.N_CITY; Sorry, I think that's a little closer. It will sum by name, city which would be correct provided every name, city is unique - not likely. It cannot include the comments and you may want to add an order by clause. Phil Phil
    54,090 pointsBadges:
    report
  • philpl1jb
    Ohh - you are trying to join 5 comments with 4 adjustment records on the taxbill_no - you will get 20 output records -- but you might want 4. Ideally the adjustment record and the comment would have a comment number field so you can join these files on the taxbill_no and the comment#. With what you have you could join them on the taxbill_no and the amount giving 3 records or the taxbill_no and the credit giviing 1 more record but this plan will fail if there are two payments for the same amount or two credits for the same amount. So there is no way I can see to get the adjustments linked with the comments without a database change.
    54,090 pointsBadges:
    report
  • JennyMack
    Phil and Tom, Thanks for contributing so much to this answer! Lorit, let us know if they've helped you reach a solution. Thanks, Jenny
    4,280 pointsBadges:
    report
  • lorit
    Thank you Phil and Tom! I appreciate your help and quick response.
    365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: