Re: Puzzling SQL Statement

["Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>]

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