Re: Query to QMQRY help...

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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