RE: Query to QMQRY help...

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.