Re: Query to QMQRY help... -- MIDRANGE-L

Tom

Just for fun, try using STRQMQRY and specify ALWQRYDFN(*ONLY) - see what it does - - specify the QRYDFN for both the QMQRY and QMFORM parameters.

There are differences between Query for IBM i and query management - you seem to have found one.

Also just for fun, try running the ANZQRY command against the QRYDFN and see if it tells you anything - there's a chapter in the QM Programming guide with some of the issues. Messages are in the QWM2301-QWM2399 range.

Summary-only is problematic, as I recall. Here is the section just before what you quoted -

If a QRYDFN object intended to produce summary-only output is suitably defined, query management will convert query information into an SQL SELECT statement with a GROUP BY clause and a field selection list containing grouping columns or column functions (for report break control fields and any selected summary functions). If no report break is defined, only column functions are used. If a QRYDFN object is not suitably defined for this mode of conversion, detail records will not be omitted. SQL summary conversion will cause a run-preventing error if any of the following applies:

* Summary function other than COUNT for a result field with no file
field reference
* MIN or MAX for a character field wider than SQL allows
* Total break fields larger than SQL allows
* Total break and summary columns larger than SQL allows

So I don't know - since you don't get the GROUP BY that is suggested here, there is something about your query that prevents it.

BTW, what version of the OS are you running? There were some PTFs for QM in 2011 that fixed a long-standing issue with converting the type 2 and 3 joins.

V5R4SI42309
6.1SI42345
7.1SI42363

Maybe they did more stuff.

Vern

On 7/24/2012 3:12 PM, Tom Hightower wrote:

Oh! Look what I found in the manual:

: Summary-only output from query management cannot be added to a file that
was created by an earlier release of IBM Query for i5/OS running the same
QRYDFN object. This is because IBM Query for i5/OS creates the file format
with extra fields for holding level and overflow feedback information, which
query management does not provide or leave room for.

So... I reckon I can't *directly* do what I was wanting to do...

TomH

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Hightower
Sent: Tuesday, July 24, 2012 2:42 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Query to QMQRY help...

Yep, I did specify the form name.

Here's what I've done (each step):
- deleted the outfile: location/blackactiv
- RTVQMFORM QMFORM(QUERY/BLACKACTIV) SRCFILE(TEST/QQMFORMSRC)
ALWQRYDFN(*YES)
---- message: Form derived from *QRYDFN object BLACKACTIV in QUERY, code 00.

- RTVQMQRY QMQRY(QUERY/BLACKACTIV) SRCFILE(TEST/QQMQRYSRC) ALWQRYDFN(*YES)
---- message: Query derived from *QRYDFN object BLACKACTIV in QUERY, code
00.

- CRTQMFORM QMFORM(TEST/BLACKACTIV) SRCFILE(TEST/QQMFORMSRC)
- CRTQMQRY QMQRY(TEST/BLACKACTIV) SRCFILE(TEST/QQMQRYSRC)
- STRQMQRY QMQRY(TEST/BLACKACTIV) OUTPUT(*OUTFILE) QMFORM(TEST/BLACKACTIV)
OUTFILE(LOCATION/BLACKACTIV)

- fields in the newly-created file location/blackactiv:
---- GTCOMPNY, GTLOCATN, GTACCTNO, ACTIVEACCT

When I run the query (RUNQRY), it generates two other fields - BREAKLVL and
OVERFLOW - at the start of each record. I'm trying to learn how to get those
two additional fields into the file generated by QMQRY. Hopefully the link
will get me in the right direction.
RUNQRY QRY(QUERY/BLACKACTIV) QRYFILE((WORK/GLTRAN)) OUTTYPE(*OUTFILE)
OUTFORM(*SUMMARY) PRTDFN(*NO) OUTFILE(LOCATION/BLACKACTIV *FIRST *NEWFILE)
TomH

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Tuesday, July 24, 2012 1:17 PM
To: Midrange Systems Technical Discussion
Subject: Re: Query to QMQRY help...

Tom

I think the problem is, output to a data file will not have the breaks, so
far as I know. Only when output is the screen or print.

And the obvious question - you DID specify the form on the STRQMQRY command,
right? Sorry - had to ask!!

The manual is available on any of the InfoCenters - the name is Query
Management Programming - the 7.1 link to it is -

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzatc/sc415703
.pdf

Enjoy!! And I dare you to write QMFORM source yourself!! I never do!

When you run RTVQMFORM, you should also look in the job log for messages
that can indicate possible issues. The messages are QWMxxxx - I forget the
range, I have it in a presentation.

There is also the ANZQRY command - you can run this before any RTVQM*
commands - it'll give you those same messages.

Then you can also look at the form by using Query Manager - STRQM option
2 takes you to the forms. Option 1 is the queries. I suggest opening the
query first, then take F13 from the editor of the query, which toggles to a
form - you want to specify the form there, since you already have one - then
it's linked - at least briefly - to the output of the query.

A manual for STRQM is located at -

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzatc/sc415212
.pdf

Regards
Vern

On 7/24/2012 11:54 AM, Tom Hightower wrote:

I've retrieved the QMFORM source, compiled the source as a form, and
run the STRQMQRY to get the report results to a file - sill no
BREAKLVL or OVERFLOW fields in my output file.

Do you know of a link to the manual?

Thanks,
TomH

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Gary Thompson
Sent: Friday, July 20, 2012 2:54 PM
To: Midrange Systems Technical Discussion
Subject: RE: Query to QMQRY help...

Tom,
there is a QM Form component that just might get you there - but you
will need to go to the manual and do a little self-help.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Hightower
Sent: Friday, July 20, 2012 1:51 PM
To: Midrange Systems Technical Discussion
Subject: Query to QMQRY help...

I have a query which uses as input one file, summarizes a single
amount field with a break level by dept#, and a final total:

CRLOCATN CRAMOUNT
397
TOTAL 67,885.83
399
TOTAL 90,068.23
FINAL TOTALS
TOTAL 157,954.06

The *outfile for this is:
*...+....1....+....
1 39700000006788583
1 39900000009006823
0 00000000015795406

Field 1: BreakLvl, pos 1-1
Field 2: Overflow, pos 2-2
Field 3: Dept#, pos 3-5
Field 4: CRAMOUNT01, pos 6-19

If I do a RTVQMQRY to extract the query statements into an SQL source
file, I get this:

H QM4 05 Q 01 E V W E R 01 03 12/07/20 14:39 V 1001 050 to pull voided
checks to add back for AP V 5001 004 *HEX SELECT
ALL CRLOCATN, (CRAMOUNT)
FROM WORKFILE T01
WHERE CRSTATUS = 1
AND CRCASHCN*10000.+CRCASHYR*100.+CRCASHMO =
YEAR(CURRENT DATE-1 MONTH)*100.+MONTH(CURRENT DATE-1 MONTH)
AND CRWRITCN*10000.+CRWRITYR*100.+CRWRITMO <>
YEAR(CURRENT DATE-1 MONTH)*100.+MONTH(CURRENT DATE-1 MONTH)
ORDER BY 001 ASC

Some stuff is missing there, and I can fix it up so that it's like so:
H QM4 05 Q 01 E V W E R 01 03 12/07/20 14:39 V 1001 050 to pull voided
checks to add back for AP V 5001 004 *HEX SELECT
ALL ' ' AS BREAKLVL, ' ' AS OVERFLOW, CRLOCATN, SUM (CRAMOUNT)

FROM WORKFILE T01
WHERE CRSTATUS = 1
AND CRCASHCN*10000.+CRCASHYR*100.+CRCASHMO =
YEAR(CURRENT DATE-1 MONTH)*100.+MONTH(CURRENT DATE-1 MONTH)
AND CRWRITCN*10000.+CRWRITYR*100.+CRWRITMO <>
YEAR(CURRENT DATE-1 MONTH)*100.+MONTH(CURRENT DATE-1
MONTH) GROUP BY CRLOCATN
ORDER BY 001 ASC

Which, when run, the QMQRY gives this file:
*...+....1....+....
39700000006788583
39900000009006823

Note that the breaklvl is blank, and there is no final total line in
the file (or on display if I run the query that way). How do I get
the output of the QMQRY file to be the same as the output of the
STRQRY

file?

TomH