|
midrange.com
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 <midrange-l@xxxxxxxxxxxx>
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.
> > >
> > >
> >
> > --
> > 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.
>
--
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.
