Using Query/400 to create SQL-style GROUP BY
Greetings and salutations,
I'm trying to put together a query for one of my users to track sales numbers and it is starting to give me a headache. I've put together a query using Query/400 with a break at each invoice to sum the dollar amount for each one.
The issue is that on many of our invoices, the same product appears on separate lines. My user wants the crunch the identical products into a single line.
So, from this:
Invoice 1
Part # Quantity
A 17
A 32
To this:
Invoice 1
Part # Quantity
A 49
Is this possible within Query/400? Unfortunately, I don't have access to create a view in SQL to do this for me. Any help would be greatly appreciated.
Thanks,
Matt
Software/Hardware used:
Software/Hardware used:
ASKED:
September 17, 2004 3:52 PM
UPDATED:
October 15, 2009 6:40 AM
Answer Wiki:
something like this:
select invoiceno, partno, sum(quantity) as qty
from yourtable
group by invoiceno, partno
==============================================================
The simple answer is not to use Query/400 to do SQL functions -- use SQL reporting. That is, create a Query Manager query instead. At least, that's the simple answer if the SQL Development Kit is on the system.
Query/400 has been obsolete for what must be 15 years by now. QM queries and forms have long surpassed the capabilities of Query/400. Even if neither Query/400 nor the SQL Development Kit is installed, QM queries and forms can still be created. The <a href="http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415703.pdf">Query Management Programming</a> manual describes the source language.
However, there are shortcuts by way of CRTQMQRY, DLTQMQRY, RTVQMQRY and WRKQMQRY, as well as CRTQMFORM, DLTQMFORM, RTVQMFORM and WRKQMFORM. These are on all systems.
And if Query/400 is available, much of the basic QM query and form work can be done with it. RTVQMQRY and RTVQMFORM can retrieve enough of the Query/400 definition into QM source members to provide a start.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
It seems you need to add a break at each item number also to total the quantities. Where is this breaking down for you?
You can also do it with 2 queries: The 1st with a break on invoice and item number but output to a dadabase file and use the “summary only” option (2). The 2nd query would use the database file you just created that is summarized by invoice and item number.