Re: Query to QMQRY help...

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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