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_
midrange.com
