Using Query/400 to create SQL-style GROUP BY
Home > IT Answers > AS/400 > Using Query/400 to create SQL-style GROUP BY
Kyoojin
0 pts.
0
Q:
Using Query/400 to create SQL-style GROUP BY
OS/400, iSeries, i5, SQL, Mainframe
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
ASKED: Sep 17 2004  3:52 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
TomLiotta
7355 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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 Query Management Programming 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
Last Answered: Oct 15 2009  6:40 AM GMT by TomLiotta   7355 pts.
Latest Contributors: r937   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SBrennecke   0 pts.  |   Sep 17 2004  5:15PM GMT

It seems you need to add a break at each item number also to total the quantities. Where is this breaking down for you?

 

rmagnan   0 pts.  |   Sep 20 2004  9:27AM GMT

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.

 
0