RE: Summary results in SQL

   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 <midrange-l@xxxxxxxxxxxx>
     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" <CWilt@xxxxxxxxxxxx>

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