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