RE: Summary results in SQL

LOL Paul

My description must have sounded pretty bad! It is not such a hassle, actually. 
And SEQUEL from ASC is a fine product, too.

Vern

-------------- Original message -------------- 
From: pnelson@xxxxxxxxxx 

What a hassle. Go try a copy of SEQUEL. www.asc-iseries.com All this is 
built in. 
-- 

Paul Nelson 
Arbor Solutions, Inc. 
708-670-6978 Cell 
pnelson@xxxxxxxxxx 
-----midrange-l-bounces@xxxxxxxxxxxx wrote: ----- 

To: Midrange Systems Technical Discussion 
From: vhamberg@xxxxxxxxxxx 
Sent by: midrange-l-bounces@xxxxxxxxxxxx 
Date: 11/01/2006 01:04PM 
Subject: RE: Summary results in SQL 

With QM forms it is possible to get some interesting results. A QMFORM 
is like the presentation side of Query/400 queries - QMQRYs are the data 
retrieval part of Query/400. You can specify summary functions in 
QMFORMs - it is a little tricky to get a summary-only equivalent. Query 
Manager is the preferred tool for generating QMFORMs, as the tag 
language used is not for the faint of heart. But see the Query 
Management Programming guide for more about this, as well as several 
nice examples. 

-------------- Original message -------------- 
From: "Wilt, Charles" 

Short answer...No. 

Long answer, SQL expects the query tool being used to provide the 
summary rows you are looking for. 

For a green screen equivalent, try Query Manager (STRQM). In query 
manger, you'd specify the query as: 
SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 

Then in the Query Manager Form, you'd specify the Column usage as SUM. 
When you run the query with the specified form, you'd get detail and 
summary lines on the report. 

Showcase should allow you the same capacity. 



In order to get a results set that actually contains detail and 

summary 

rows, you have to use a UNION. 
SELECT DISTINCT 
TPROD, 
sum(TQTY), 
sum(TQTY) * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 
Group by TPROD 
UNION 
SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 

HTH, 

Charles Wilt 
-- 
iSeries Systems Administrator / Developer 
Mitsubishi Electric Automotive America 
ph: 513-573-4343 
fax: 513-398-1121 

-----Original Message----- 
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
daparnin@xxxxxxxxxxxxxx 
Sent: Wednesday, November 01, 2006 11:31 AM 
To: midrange-l@xxxxxxxxxxxx 
Subject: Summary results in SQL 





This is the select statement for a Showcase query that's 
pulling data from 
the iSeries. On a typical green-screen query you can select 
summary or 
detail data from the "Select output type and output form" 
option. Can that 
be done with SQL? Basically, we just want to see a part 
number, the total 
quantity, and the total of the cost * quantity. If it helps, forget 
Showcase; how would you do this in green-screen SQL? 


SELECT DISTINCT 
TPROD, 
TQTY, 
TQTY * TSCST AS COLUMN0000 
FROM 
V40BPCSF.ITHL15 ITH 
WHERE 
TWHS IN( '11', 'HT' ) 
AND TTDTE BETWEEN &TTDTE1 AND &TTDTE1 
AND TQTY <> 0 
AND TTYPE IN( 'M', 'PR' ) 
AND TPROD BETWEEN &TPROD2 AND &TPROD2 
AND TLOCT IN( 'INXT', 'RMDI', 'DWIP' ) 


Dave Parnin 
-- 
Nishikawa Standard Company 
Topeka, IN 46571 
daparnin@xxxxxxxxxxxxxx 

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