Mike,

Ok, so FUND, DEPARTMENT, ACCOUNT, PROJECT, JOB are your "account number", and it seems that one invoice can have many account numbers.... Now, in cases where we have multiple "account numbers" for an invoice, which one should we use? That's the real trick...

There are problems with simply using min() or max() over each of the fields in your compound account number, so unless you need these values individually returned, I'd recommend stringing those together into a single field. Then you could min or max if you wanted... I don't know data-types, so I'll just assume that each is numeric..

SELECT INVOICE_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT,
MAX(DIGITS(FUND) || DIGITS(DEPARTMENT) || DIGITS(ACCOUNT) ||
DIGITS(PROJECT) || DIGITS(JOB) ) as ACCOUNT_NO
FROM INVOICEDETAIL
GROUP BY INVOICE_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, INVOICE_DATE, INVOICE_AMOUNT
ORDER BY ACCOUNT_NO

I'm not really a fan of min() or max() for things like this, because it just hides the 1 to many relationship.
YMMV,
Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Mike
Sent: Tuesday, August 05, 2008 3:01 PM
To: RPG programming on the AS400 / iSeries
Subject: Puzzling SQL Statement


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?



This thread ...

Replies:

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