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.
OHENT# EQ ADENT#
OHSFX# EQ ADSFX#
OHORD# EQ IDORD#
OHENT# EQ ACENT#
OHSFX# EQ ACSSF#
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.
midrange.com
