× 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.


  1.  Home
  2. MIDRANGE-L
  3. October 2007

RE: Summary Output with STRQMQRY

Sure.

Usage would be BREAK1 on the first column. Then on any other column, put a usage of COUNT.

HTH,

Charles


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

Well...seeing this thread made me want to hack and slash on a
QM I wrote a while back. So I want to break and produce a
count at the same time. Is that doable with QM and the USAGE
column? I want this:

ABC 10
ABC 12
ABC 20
3
DEF 10
1
GHI 8
GHI 77
2

On 10/2/07, Wilt, Charles <WiltC@xxxxxxxxxx> wrote:
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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.