Re: Puzzling SQL Statement

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