Re: Summary Output with STRQMQRY -- MIDRANGE-L

I think the "new" Web Query does pivot table like stuff....

On 10/2/07, Wilt, Charles <WiltC@xxxxxxxxxx> wrote:

Cool, what do I win? (Won a copy of Office 2007 Pro a couple of weeks
ago, so you've got some high
expectations to meet ;)

Actually, I believe QM does it the "old fashioned" way. When you indicate
a form's column is to be
summed, QM simply adds up the data as it reads the results set. Same way
you'd do it with RPG.

The big benefit, besides being easier, is that you only hit the data once
instead of twice.

But I've used the same technique you posted myself when I had a QM report
that needed daily invoice
detail, daily total, plus Month-to-date and Year-to-date.

When outputting .csv I prefer to leave off totals so the users can easily
summarize the data , in
Excel I assume, however they want. I'm a big, big fan of Excel pivot
tables. So are the users I've
shown them too.

Actually, I wish IBM would add some pivot functionality to DB2.

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Tuesday, October 02, 2007 12:50 PM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY

Ok, Charles wins.... I rarely use QM forms, generally using
QM query objects to produce outfiles destined for email as
csv attachments...... I would assume that QM is implementing
something along the lines of my CTE example.

Thanks for pointing this out.
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
Sent: Tuesday, October 02, 2007 11:33 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY

In the "USAGE" column of the QM form, specify SUM.

HTH,

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Don Cavaiani
Sent: Tuesday, October 02, 2007 12:17 PM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY

Eric - I tried this and it works well! How would the GRAND

Totals be

included?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Tuesday, October 02, 2007 9:46 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY

Not really... It's quite easy to produce summary data in SQL....

Note all the fields that you need calculate a summary result.
All of these fields will use functions like SUM(), MIN(), MAX(),
AVG() and so forth.

All other fields (Keys, control breaks, etc.) need to

specified in the

Group By clause. All of these fields in the Group By,

taken together,

represent a DISTINCT grouping, and will result in a single

row of the

result set. It works the same way as using a DISTINCT clause in a
simple select, where the DB finds every permutation of values that
exist in the DB for those columns, and returns a single row

for each

unique grouping.

Here's a basic query using the Group By... The assignment might be
worded like "Produce a listing of all active customers,

showing total

sales, cost, and a count of orders for the prior 12 months.

Sort the

list by total sales (descending)."

Select c.CustNo, c.CustName,
sum(s.InvTtl$) as TtlSls,
sum(s.InvCost) as TtlCost,
count(distinct s.Order#) as TtlOrders

From CustMst c left outer join

InvHist s on (c.CustNo=s.CustNo and s.OrDate >=

(Current_Date - 1

years)) Where c.CustStatus = 'Active'
Group By c.CustNo, c.CustName
Order By 3 desc

This query returns a row for all "Active" customers in the CustMst
file (see the Where clause), and joins to the Invoice

history file to

return Sales, Cost, and OrderCount, if any.
The two tables are joined on the CustNo fields in both

files, and is

specified in the ON clause following the table being

joined. Note the

clause with OrDate; this limits the joined record to orders dated
within the last year.

The placement of the date test is important. I put it in the ON
clause, because putting it in the Where clause would cause

our query

to eliminate any rows where the join was null.
Where clause applies to all rows in a result set. If we

were to test

s.OrDate >= (Current_Date - 1
years) in the Where clause, we would remove all customers

that had no

sales in the past year. By moving this to the ON clause, we can
evaluate the date of invoice at the time that we select the

records to

join. If a record fails to match the criteria, it is

omitted from the

joined result.

The Group By lists the control break fields, and finally,

the returned

result set is ordered descending on the third column in the result
(Total Sales).

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Fleming, Greg
(ED)
Sent: Tuesday, October 02, 2007 8:22 AM
To: Midrange Systems Technical Discussion
Subject: RE: Summary Output with STRQMQRY

We're using STRQMQRY because we need to pass a parameter,

and can't do

it with RUNQRY.

However, I reckon it may be easiest to just modify my query

to produce

a file with the detail, then create a second query to

summarize it to

printed output. Since the second query won't require a

parameter, we

can just use RUNQRY on it.

Thanks

|-----Original Message-----
|From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
|bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
|Sent: Tuesday, October 02, 2007 9:04 AM
|To: Midrange Systems Technical Discussion
|Subject: Re: Summary Output with STRQMQRY
|
|If you're using a Query/400 definition, and you're not

using RTVQMQRY

or
|some such thing, then why are you using STRQMQRY and not RUNQRY?
|If you ARE using RTVQMQRY, then you should modify the statement
produced
|and use GROUP BY.
|
|Rob Berendt
|--
|Group Dekko Services, LLC
|Dept 01.073
|PO Box 2000
|Dock 108
|6928N 400E
|Kendallville, IN 46755
|http://www.dekko.com
|
|
|
|
|
|"Fleming, Greg $ED$" <GFLEMING@xxxxxxxxxxxxxxxxxxxx> Sent by:
|midrange-l-bounces@xxxxxxxxxxxx
|10/02/2007 08:54 AM
|Please respond to
|Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
|
|
|To
|"Midrange Systems Technical Discussion"

<midrange-l@xxxxxxxxxxxx> cc

|
|Fax to
|
|Subject
|Summary Output with STRQMQRY
|
|
|
|
|
|
|We're using STRQMQRY on a Query/400 QRYDFN. The query

when run from

|Query/400 is defined to return summary records only, but
when run from
|STRQMQRY in a CL program (or interactively), it returns detail.
|
|I found a post from 2003 which indicated a similar problem, and
|suggested running RTVQMFORM to generate a source file from
the QRYDFN,
|then using CRTQMFORM to create a form to use on the

STRQMQRY QMFORM

|parameter. I tried that, but I still get detail.
|
|Is there anyway to make this thing give me summary records only ?
|
|Thanks
|
|Greg Fleming
|
|Senior Programmer/Analyst
|
|Everglades Direct, Inc.
|
|
|
|--
|This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing
|list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To

subscribe,

|unsubscribe, or change list options,
|visit: http://lists.midrange.com/mailman/listinfo/midrange-l
|or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a

|moment to review the archives at
|http://archive.midrange.com/midrange-l.
|
|
|--
|This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing
|list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To

subscribe,

|unsubscribe, or change list options,
|visit: http://lists.midrange.com/mailman/listinfo/midrange-l
|or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a

|moment to review the archives at
|http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion

(MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is
intended to be confidential and privileged. If you receive
this e-mail and you are not a named addressee you are hereby
notified that you are not authorized to read, print, retain,
copy or disseminate this communication without the consent of
the sender and that doing so is prohibited and may be
unlawful. Please reply to the message immediately by
informing the sender that the message was misdirected. After
replying, please delete and otherwise erase it and any
attachments from your computer system. Your assistance in
correcting this error is appreciated.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a
named addressee you are hereby notified that you are not authorized to read,
print, retain, copy or disseminate this communication without the consent of
the sender and that doing so is prohibited and may be unlawful. Please
reply to the message immediately by informing the sender that the message
was misdirected. After replying, please delete and otherwise erase it and
any attachments from your computer system. Your assistance in correcting
this error is appreciated.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.