Using Query/400 to create SQL-style GROUP BY

0 pts.
Tags:
i5
iSeries
Mainframe
OS/400
SQL
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: September 17, 2004  3:52 PM
UPDATED: October 15, 2009  6:40 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SBrennecke
    It seems you need to add a break at each item number also to total the quantities. Where is this breaking down for you?
    0 pointsBadges:
    report
  • Rmagnan
    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 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following