Help with Query Manager and Duplicate Values

365 pts.
Tags:
Query Manager
Below is snapshot of my SQL Query when I view it Query Manager:

SELECT DISTINCT                                                               

Software/Hardware used:
AS400, Query Manager

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 7  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Any chance we could get just a few more details? Perhaps even a description of the problem would be helpful. Tom
    125,585 pointsBadges:
    report
  • lorit
    The T16.PCTG file has a field called "FMV". I need the sum of this field. However, it is duplicating the FMV values in the query. SELECT DISTINCT -- Columns A.RD_YEAR, C.N_NAME, A.RD_PARCEL_NO, B.PP_TOWNSHIP, D.PC_FMV, A.RD_COMMENT, B.PP_PRIMARY_TAXPAYER, C.N_HOUSE_NR, C.N_STREET, C.N_CITY, C.N_STATE, C.N_ZIP_FIRST_FIVE, SUM(D.PC_FMV) -- Tables FROM "QS36F"/"T16.CRDC" A, "QS36F"/"T16.PPAR" B, "QS36F"/"T16.GNAM" C, "QS36F"/"T16.PCTG" D -- Row Selection WHERE ((A.RD_ROLL_TYPE = 'P') AND (B.PP_TOWNSHIP = 1351) AND (A.RD_STATUS_YYMMDD BETWEEN 20111101 AND 20111114)) -- Join Conditions AND (A.RD_YEAR = B.PP_YEAR) AND (A.RD_ROLL_TYPE = B.PP_ROLL_TYPE) AND (A.RD_PARCEL_NO = B.PP_PROPERTY_NR) AND (B.PP_PRIMARY_TAXPAYER = C.N_ID_NR) AND (A.RD_YEAR = D.PC_YEAR) AND (A.RD_PP_SHORT_KEY = D.PC_SHORT_KEY) -- Summary Columns GROUP BY A.RD_YEAR, C.N_NAME, A.RD_PARCEL_NO, B.PP_TOWNSHIP, D.PC_FMV, A.RD_COMMENT, B.PP_PRIMARY_TAXPAYER, C.N_HOUSE_NR, C.N_STREET, C.N_CITY, C.N_STATE, C.N_ZIP_FIRST_FIVE -- Sort Columns ORDER BY B.PP_TOWNSHIP
    365 pointsBadges:
    report
  • philpl1jb
    What do you mean duplicating the value? Is it duplicating the row or giving twice the value? Does it do it for every case? Never correct, never three times, always twice? Give an example. Here is my wild unsupported guess: I suspect that it doesn't happen all the time only when the Primary Taxpayer has more than 1 house. as in this (B.PP_PRIMARY_TAXPAYER = C.N_ID_NR) produces more than 1 row. Phil
    50,860 pointsBadges:
    report
  • lorit
    Yes, that's correct - it is duplicating the row - not the value.
    365 pointsBadges:
    report
  • philpl1jb
    With the DISTINCT keyword drop exactly duplicate rows. would be dropped. Does it happen all the time? Always two? And on the "duplicate rows" are the values of C.N_HOUSE_NR, C.N_STREET, C.N_CITY, C.N_STATE, C.N_ZIP_FIRST_FIVE the same for both? Phil
    50,860 pointsBadges:
    report
  • philpl1jb
    Sorry that wasn't English. You showed us a query with the DISTINCT keyword. That keyword would drop rows where all fields were exactly duplicate. So I suspect you're seeing 1, 2, 3, 4 rows for the same person - different parcels or different house numbers but with the same value of SUM(D.PC_FMV).. Phil
    50,860 pointsBadges:
    report
  • TomLiotta
    One odd problem is that D.PC_FMV is both a selected column and a SUM() column. That makes no sense. It doesn't make sense to have a column both as a detail selected column and as a sum. Tom
    125,585 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following