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.
