MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2006

Re: Summary results in SQL



fixed

Dave--

what Rob said, plus a couple of other observations...

- when the lib.file is changed to lib/file, the suffix "ITH" will be interpreted as a identifying correlation
  - believe this was a record format or member name in the other syntax
- here it will probably not be needed, if it is present will need to prefix all columns with "ITH." - on the BETWEENs, this is specifying a range with the same value for both the from and to - if this is what's wanted, will be better with "AND TTDTE = &TTDTE1", "AND TPROD = &TPROD2" - the & prefixed substitution variables will work just fine in QM (Query Manager) <g>

thx & hth,

--Jerome

On Nov 1, 2006, at 10:59 AM, rob@xxxxxxxxx wrote:

Sort of like:
SELECT TPROD,
      sum(TQTY) as TotalQty,
      sum(TQTY * TSCST) AS TotalCost
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


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





daparnin@xxxxxxxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
11/01/2006 11:31 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact