In the email you say you do not want duplicate invoices listed. The
reason that your original SQL is listing duplicate invoices is because
the invoice number has two or more records with different value in one
of the CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT,
FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB fields. You need to tell us
what you want SQL do when this happen.

For example, if your InvoiceDetail file contains below:
INVOICE_NUMBER JOB
12345 AAAA
12345 ZZZZ
45678 BBBB
45678 CCCC

What do you want the result of the SQL to look like. Even if you do not
want to see value of the JOB field in the result, you still need to tell
us which JOB value do you want the SQL to sort by? If you can clarify
that, someone on the list should be able to help you.


"Mike" <koldark@xxxxxxxxx> wrote in message
news:<mailman.1857.1218039482.2545.rpg400-l@xxxxxxxxxxxx>...
FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB are all a part of the account
number. As I said in the previous email, this is a view that I created
that
joins the header, items, and accounts files together in one big query.

--
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


On Wed, Aug 6, 2008 at 10:13 AM, Lim Hock-Chai <
Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote:

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 is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



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].