RE: Puzzling SQL Statement

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?