Re: Summary results in SQL

Thanks Jerome.  Like I said to Rob, my SQL has gotten quite a bit better
but I've still got much to learn.  This will help.


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



                                                                                
                                                          
                      Jerome Hughes                                             
                                                          
                      <jromeh@xxxxxxx>            To:       Midrange Systems 
Technical Discussion <midrange-l@xxxxxxxxxxxx>               
                      Sent by:                    cc:                           
                                                          
                      midrange-l-bounces@m        Subject:  Re: Summary results 
in SQL                                                    
                      idrange.com                                               
                                                          
                                                                                
                                                          
                                                                                
                                                          
                      11/01/2006 12:26 PM                                       
                                                          
                      Please respond to                                         
                                                          
                      Midrange Systems                                          
                                                          
                      Technical Discussion                                      
                                                          
                                                                                
                                                          




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.

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