RE: Puzzling SQL Statement

What about using the group by clause?

SELECT INVOICE_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT, FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB
FROM INVOICEDETAIL
Group by INVOICE_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT, FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB
ORDER BY FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB

If this returns duplicate invoices then your returned fields really aren't all the same and therefore not a duplicate.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Sent: Tuesday, August 05, 2008 3:01 PM
To: RPG programming on the AS400 / iSeries
Subject: Puzzling SQL Statement

I am working on figuring out a fix for a bug that came up after I added a feature the user requested. I don't want duplicate invoices listed, but they now are. This is the problem SQL statement.

SELECT DISTINCT INVOICE_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT, FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB FROM INVOICEDETAIL ORDER BY FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB

I know the problem and why the problem is there (the FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB is the account number for the item), but my question is how can I fix it? I can't remove those fields from the select because then the ORDER BY doesn't work. Any ideas on how I can pull this query off?


--
Mike Wills
Midrange Programmer/Analyst

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.