Re: Puzzling SQL Statement -- RPG400-L

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.