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