On 26-Dec-2013 14:59 -0800, Hoteltravelfundotcom wrote:
I have been unable to duplicate this query.

So we should infer "this query" is a Query/400 Query Definition (*QRYDFN) object type. And the desire "to duplicate" is with regard to effecting the /conversion/ implied in the subject. And that the desired /conversion/ is into an SQL SELECT, per the final comment quoted below. Because of course, to /duplicate/ a *QRYDFN object is just either of CRTDUPOBJ or option 3=Copy in the Work With Queries (WRKQRY) feature.

Describing a manner in which one might effect /conversion/ from the QRYDFN to the SQL, requires knowing almost the full QRYDFN rather than merely some subset of information one has chosen to share about the QRYDFN.

It uses 5 files it matches from the order history file to customer,
and shipping, invoice history and address files.

has some record selects and sorting, no problems so far. Does totals
on qty shipped and $shipped.

Some attributes for /sorting/ and possibly selection, capabilities that are available to the Query/400, are not directly available to the SQL. Generated rows, from join types that require the capability, are effected by producing the NULL value for an SQL query. But as I recall, the Query/400 generates default values for generated rows. Presumably we can infer that the JOIN, WHERE, and ORDER BY have been verified as satisfactory for /detail/ reporting using the SQL SELECT [without any aggregation]; i.e. the detail result sets are identical betwixt.? The chosen Collating Sequence and the chosen Processing Options (with regard to the chosen collating sequence) are relevant with regard to the possibility of the SQL SELECT to mimic the Query/400. Presumably we can infer the collation for selection and ordering is not an issue per verified consistency from the Sort Sequence (SRTSEQ) of the SQL request; again, that the result set is verified to be identical betwixt the detail [non-summarized] QRYDFN and the SQL SELECT result sets.?

Knowing the result-sets of the non-aggregate queries are identical, i.e. the SQL and the non-SQL queries, implies that any knowledge of the JOIN, ORDER, and WHERE is unnecessary.

Does break levels on 9 fields. this is the part I cannot translate.
then puts the data to a temp file.

There is no direct equivalent in the SQL, to Report Breaks of the Query/400. The Grouping feature of the SQL produces grouped rows [versus being a reporting feature] typically with aggregates, else it is just a DISTINCT query against grouped columns. The Report Breaks feature of the Query/400 is effectively just a reporting feature; albeit actually producing rows, though not necessarily grouped rows, when using effectively the RUNQRY OUTFORM(*SUMMARY) OUTTYPE(*OUTFILE). However, although Breaks and Groups are inherently different, they often are used for effectively equivalent results. The implication of the use of an output file for the QRYDFN reporting suggests the probability of /summary-only/ processing, so we can presume there is no need to include reporting for both detail and summary data in the same SQL SELECT. If the sequence of the fields for the /Select Sort Fields/ of the QRYDFN reporting, matches the sequence of the fields as defined for /Report Breaks/ for the QRYDFN reporting, then the SQL grouping with columns specified in the same order will most closely match the /breaking/ of that data. But then a big question remains, whether the breaks are differing levels, or are they the same level, or some combination? It's quite difficult to know what is the Query Definition, without seeing the printed definition; i.e. simply saying "break levels on 9 fields" is insufficient.

As Vern mentioned, the Analyze Query Definition (ANZQRY) and the Retrieve QM Query (RTVQMQRY) and the Retrieve QM Form (RTVQMFORM) may be of some assistance first in determining what will not make the conversion from the *QRYDFN object into the *QMQRY and *QMFORM object types. Then against which respectively, Create QM Query (CRTQMQRY) and Create QM Form (CRTQMFORM) can be issued, as an attempt to make some attempt at a possible equivalent SQL-implemented query request that can be run via the Start QM Query (STRQMQRY) CL request. But only if the reported limitations are not show-stoppers, though I believe they will be. And the analyze feature is effective only for the query, not the report form. However the Start QM (STRQM) Cl request can start an interactive query and form utility, and then used to build a form that matches a modified version of the source SQL SELECT statement for QMQRY. That might be a better approach. I am not sure, but there may be a limit of six break levels with the QM report writer; i.e. Usage specification in the Edit Column Formatting seems limited from BREAK1-BREAK6. Yet, I do not recall any support for summary-only, if even any capability to include summary results to a database output file; i.e. the QMQRY separation of query and report writing is even more explicit than the QRYDFN, so that /reporting/ feature carried by QRYDFN into its summary-only OUTFILE capability is likely unique to that utility.

I am not getting the same number of records no matter what. am sure
the issue is how to code this Break in SQL.

Only grouping can be coded in the SQL, not breaks; i.e. there is no capability to "code this Break in SQL." Thus already, we have a good reason why they might not match. Again, that is because /Breaks/ are a reporting feature, and the SQL is not a reporting tool; although some reporting tools that implement with the SQL, may provide a /Break/ feature; e.g. QMQRY. Aggregates come from grouped data sets in the SQL SELECT whereas aggregates will come from /breaks-in-prior-data/ in the QRYDFN.

The Query/400 produces a summary\aggregate row for each /break-level/ for changes in the data across the [nine] fields selected for /break/ processing. The SQL produces a summary\aggregate row for each /group/ using an implied ordering for the determination of a grouped value and the SQL enables further selection on a HAVING clause. The QRYDFN can specify different ordering for detecting breaks, than what is implied for detecting groups in the SQL, and can even have unpredictable ordering. Given identical collation and selection, with a grouping-field replacing each break-field at a unique level, there will be at least one fewer row via the SQL than the Query/400 OutFile because the latter always includes an overall summary row. For each column in the grouping list prior to the last named [a QRYDFN implies /columns/ vs /expressions/ will be used, because its expressions are, unlike with the SQL, defined previously as columns using the query /Define Result Fields/ capability], no aggregate rows will be produced for those sub-groups when using the simple GROUP BY clause. However when the WITH ROLLUP is coded [or an equivalent using either GROUP BY ROLLUP or GROUP BY GROUPING SETS using the multiple grouping-sets], subgroups can also generate a summary row [with the NULL value for the columns not being grouped]. Because the option to /suppress summaries/ in the /Report Breaks/ of a QRYDFN will not impact the output file results [i.e. all summary rows will be produced for OUTFORM(*SUMMARY) OUTTYPE(*OUTFILE) irrespective the /suppress/ setting], the use of ROLLUP is easiest... *if* the output of the QRYDFN is effectively identical to that type of SQL SELECT query. The overall summary row where BREAKLVL='0' is the equivalent to the empty grouping-set for which the aggregate would be effectively identical, irrespective of ordering.

As an effect of the SQL query SELECT vs that of a report writer, the grouping-sets result set may be sufficient to mimic a summary-only QRYDFN run with an Output Type (OUTTYPE) of *OUTFILE. However assigning a break-level to each row is not as straightforward, if mimicking that effect of the QRYDFN is also required.

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 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].