× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



   There's the rub. Somebody's got to program something. I say let the users
   do their own stuff. After learning the proper ways, of course.
   -- 

   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:45PM
     Subject: RE: Summary results in SQL

     Right - but I was talking Query Management, not Query/400 - it works
     best when you use a CL command front end for it but you have run-time
     variables and all that there, too. For those that have the SQL Dev Kit
     and Query Manager, they might not want also to buy another product - QM
     stuff is a good option - much better than Query/400 for reasons you
     state. ASC sends me blurbs about SEQUEL for webinars and rightly speaks
     of being better than Query/400 - nothing said about QM.

     Later my friend - thinking of lefse and fattigmand again!

     -------------- Original message --------------
     From: pnelson@xxxxxxxxxx

     > It's not your description. It's the tool. SEQUEL makes things so much
     > easier. I sat here at a client site yesterday and observed a lady
     > wrestling with Query/400. She was running sales reports for selected
     > regions. For each region, she had to select a menu option, which fired
     up
     > Query. She then had to manually change the region code and the report
     > heading, and then hit the function key to run the report.
     >
     > With SEQUEL, this can done with run-time variables. The user can click
     an
     > icon, get a pull-down list of region codes, select the desired code,
     and
     > click run.
     > Piece of cake.
     > --
     >
     > Paul Nelson
     > Arbor Solutions, Inc.
     > 708-670-6978 Cell
     > pnelson@xxxxxxxxxx
     > -----midrange-l-bounces+pnelson=arbsol.com@xxxxxxxxxxxx wrote: -----
     >
     > To: Midrange Systems Technical Discussion
     > From: vhamberg@xxxxxxxxxxx
     > Sent by: midrange-l-bounces+pnelson=arbsol.com@xxxxxxxxxxxx
     > Date: 11/01/2006 01:10PM
     > Subject: RE: Summary results in SQL
     >
     > LOL Paul
     >
     > My description must have sounded pretty bad! It is not such a hassle,
     > actually. And SEQUEL from ASC is a fine product, too.
     >
     > Vern
     >
     > -------------- Original message --------------
     > From: pnelson@xxxxxxxxxx
     >
     > > 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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.