RE: Query to QMQRY help...

Thanks much!

TomH

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

I forgot - you specify the form on the STRQMQRY command - and you use
CRTQMFORM to create it - big doh there, eh?

VErn

On 7/20/2012 3:06 PM, Vernon Hamberg wrote:

Tom

You should also use RTVQMFORM against the QRYDFN - QM separates
presentation from data retrieval, so you need the form to get the layout.

Once you have the form, you should use exactly what RTVQMQRY gave you
- don't add those other fields.

Also, you probably don't have any issues, but check the messages in
the joblog for any QWMxxxx messages that report possible problems.

HTH
Vern

On 7/20/2012 2:50 PM, Tom Hightower wrote:

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.