× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks to Chuck and Vernon for your very informative replies.
I quickly added GROUP BY to the SQL part of the QM Query so I have an acceptable report.
Today did not go as planned so I've just now quickly reviewed and will take more time to digest . . .


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Saturday, February 28, 2015 8:27 AM
To: Midrange Systems Technical Discussion
Subject: Re: Query Manager Totals Only

I see that this works fine for a final-totals report - but not for subtotals - the contributing detail is included in the *QMQRY.

This is because there are no aggregate functions in this conversion - more on this in a minute.

So the resulting query for a level break on the STATE field came out like this:

SELECT
ALL STATE, (CDTLMT), (BALDUE), (CDTDUE)
FROM QIWS/QCUSTCDT T01
ORDER BY 001 ASC

Then subtotals are done in the *QMFORM.

The statement could be changed to have GROUP BY STATE and have SUM() functions on the 3 fields - interesting that the parentheses are there, as if someone KNEW they needed the SUM() function.

So here's the revision I tried:

SELECT
ALL STATE, SUM(CDTLMT), SUM(BALDUE), SUM(CDTDUE)
FROM QIWS/QCUSTCDT T01
GROUP BY STATE
ORDER BY 001 ASC

With a little tweaking of the *QMFORM I got this result:

TOTAL TOTAL TOTAL
Credit Balance Credit
ST Limit Due Due
----- ---------- ------------- -------------
CA 700 250.00 100.00
CO 400 58.75 1.50
GA 9,999 3,987.50 33.50
MN 14,999 510.00 .00
NY 2,100 589.50 .50
TX 5,200 62.00 .00
VT 700 439.00 .00
WY 9,999 .00 .00
========== ============= =============
TOTAL 44,097 5,896.75 135.50

This is probably what Sam's suggestion could have generated.

Another thing to do is run ANZQRY over the *QRYDFN - I got the message
QWM2323 - Detail records will not be omitted.

In that message are things to do to get the summary only. One is to omit final summaries in the *QRYDFN. I tried this and eventually got this:

TOTAL TOTAL TOTAL
Credit Balance Credit
ST Limit Due Due
------------ ---------- ------------- -------------
CA 700 250.00 100.00
CO 400 58.75 1.50
GA 9,999 3,987.50 33.50
MN 14,999 510.00 .00
NY 2,100 589.50 .50
TX 5,200 62.00 .00
VT 700 439.00 .00
WY 9,999 .00 .00

FINAL TOTALS 44,097 5,896.75 135.50

So there are options - with a little tweaking it might be what you want.

HTH
Vern

On 2/28/2015 8:41 AM, Vernon Hamberg wrote:
Hi Gary

I used RTVQMQRY and RTVQMFORM to pull the *QRYDFN into source. Then I
created the *QMQRY and *QMFORM - I got a summay-only result.

This was on a V5R3 box, so it should work for anyone.

You need to have created and then include the *QMFORM when running the
*QMQRY.

I tried a simple *QRYDFN over QIWS/QCUSTCDT with the following
relevant items:

Selected files
ID File Library Member Record Format
T01 QCUSTCDT QIWS *FIRST CUSREC
Report column formatting and summary functions
Summary functions: 1-Total, 2-Average, 3-Minimum, 4-Maximum,
5-Count Overrides
Field Summary Column Dec Null Dec Numeric
Name Functions Spacing Column Headings Len Pos
Cap Len Pos Editing
CDTLMT 1 2 Credit 4 0 4 0
Limit
BALDUE 1 2 Balance 6 2 6 2
Due
CDTDUE 1 2 Credit 6 2 6 2
Due
Selected output attributes
Output type . . . . . . . . . . . . . . Display
Form of output . . . . . . . . . . . . Summary only

The output from running this *QRYDFN was this:

Credit Balance Credit
Limit Due Due
FINAL TOTALS
TOTAL 44,097 5,896.75 135.50

The output from running the *QMQRY with the *QMFORM (STRQMQRY
QMQRY(VHAMBERG1/TOTALSONLY) QMFORM(*QMQRY)) was this:

TOTAL TOTAL TOTAL
Credit Balance Credit
Limit Due Due
---------- ------------- -------------
44,097 5,896.75 135.50

The SELECT statement created by RTVQMQRY was this:

SELECT
ALL SUM(CDTLMT), SUM(BALDUE), SUM(CDTDUE)
FROM QIWS/QCUSTCDT

You would add substitution variables to a WHERE clause here.

HTH
Vern

On 2/27/2015 4:56 PM, Gary Thompson wrote:
Easy to do in a *QRYDFN but I'm not seeing how to set a *QMQRY to
print only totals ?

We have a local command that converts *QRYDFN to *QMQRY which I used
to create a *QMQRY version that I can pass parameters to so users can
create a totals only report, but have yet found how to do that for
the Query Manager report.




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.