RE: Summary results in SQL

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.