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_