Are you saying that you invoiceDetail file actually contained
Customer_Number, Customer_Name...? If it does, that is a strange
design. I would think those fields should be in invoice header file.
But anyway, your email seems to indicate that your invoiceDetail file
could contain multiple records for a invoice number and those multiple
records of a invoice number could contains different value on
CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT, FUND,
DEPARTMENT, ACCOUNT, PROJECT, JOB fields. If that is the case, you will
need to tell SQL how you want to handle it when a invoice number is
found in two or more records and these two or more records has different
CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT, FUND,
DEPARTMENT, ACCOUNT, PROJECT, or JOB value.

For example, if your InvoiceDetail contains below:
INVOICE_NUMBER JOB
12345 XXXX
12345 YYYY


What JOB value do you want sql to show you for this one invoice number?




"Mike" <koldark@xxxxxxxxx> wrote in message
news:<mailman.1660.1217966486.2545.rpg400-l@xxxxxxxxxxxx>...
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

Sick of corporate radio and hungry for something new?
http://thenextgenerationofradio.com
Stalking me? http://twitter.com/MikeWills |
http://friendfeed.com/mikewills

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].