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