Re: old query issue

Hi HTF

In summary -

1. Use ANZQRY to see what issues you may have - there are more things
different than just the summary-only feature of *QRYDFNs.
2. Get the Query Management Programming manual - last modified slightly at
6.1 - especially Chapter 12, which has the information about using or
converting Query for i queries to QMQRY & QMFORM
3. Check out Chapter 11 for general considerations and Appendix C for
handling quotation marks and apostrophes
4. In addition to the message Chuck cited (QWM2323) take a look at all the
message between QWM2300 and QWM2399 - this'll give you good ideas about
what you might see with ANZQRY.
5. Get the Query Manager Use manual - this has lots of how-to stuff for
using that particular interface to QM - there are also the WRKQMQRY and
WRKQMFORM commands, if you like.
6. Type GO CMDQM to see what the system commands you have for Query
Management.

You're on a worthy and worthwhile quest, I think - good wishes your way!

Vern


On 4/30/2015 7:45 PM, CRPence wrote:

On 30-Apr-2015 17:27 -0500, Hoteltravelfundotcom wrote:

<<SNIP>>

When I convert this via RTVQMQRY I notice that this is not doing the
break totals used in the query

Use Retrieve Query Manager Form (RTVQMFORM) to extract reporting
specs. The Retrieve Query Manager Query (RTVQMQRY) extracts the data
retrieval portion only; as an SQL SELECT.

<ed: Select Report Summary Functions; 1=Total aka SUM()>

1 VALUE
T01.IDENT#
SHIPTO
1 WEIGHT

<ed: Define Report Breaks>
Break Sort
Level Prty
1 10

Conspicuously missing from the above is the name of the field on which
the Break is defined [and on what the record data is being collated
primarily]. Referred to henceforth as break_field

<ed: Select Output Type and Output Form; Form of output:>
2 1=Detail
2=Summary only

but I am not getting summary only in the view derived from this
query.

The Analyze Query (ANZQRY) would have informed of that limitation with
the msg QWM2323 "Detail records will not be omitted." The data retrieval,
the query must be [modified to be] composed as an aggregate query, and an
appropriate report-form designed to match that query.

My question is, should I convert the Query/400
<ed: Query Definition (QRYDFN) object> to the newer query Management
query <ed: (QMQRY) object> if there is such a conversion tool, and if
doing this, will give me a more exact view with regards to same
data.

The Start Query Management (STRQM) to just create both the query and
the form [or just use a default form] for that query might be simpler; e.g.
such as:

select
break_field
, dec(sum("VALUE"), 19, 2) as VALUE01
/* qry sum aggregates: p=col_prec+3 s=col_scale */
, dec(sum(weight ), 19, 2) as WEIGHT01
from the_file
group by break_field
union all
select
cast( null as /* data-type of break_field */ ) as break_field
, dec(sum("VALUE"), 19, 2) as VALUE01
/* qry sum aggregates: p=col_prec+3 s=col_scale */
, dec(sum(weight ), 19, 2) as WEIGHT01
from the_file
order by break_field

Or if a *QRYDFN object is acceptable, then the above SELECT query could
be encapsulated in a VIEW [without the ORDER BY clause] and the query
defined to specify for /Select Sort Fields/ the break_field column.

An alternative may be available using GROUPING SETS or ROLLUP
capabilities instead, but then the possibility to use the Query/400 report
writer may be inhibited per reference to a VIEW taking advantage of those
features.? Such a query could be coded directly for the QMQRY, for which a
default form may suffice.
<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzgroupbyclause.htm>

_group-by-clause_

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