STRQMQRY not sum up the right total
I try to use STRQMQRY to pass in a variable to run a query. It works great except the query suppose to have a grand total, for some reason, it doesn't add up right. When i run the query manually without using STRQMQRY, it works fine. Anyone know what do I have to do here? Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
November 2, 2005 9:39 AM
UPDATED:
November 17, 2009 8:01 AM
I found that out now when i convert the query definition to QM with join files, when i use join type match with primary file, it actually drop the record in primary if the record is not match with the second file or third file.
I can use left join after i convert it to QM source, but is that any way to make it works without have to change the join statement and convert the QM to source?
ie after rtvqmsrc:
H QM4 05 Q 01 E V W E R 01 03 05/11/02 16:39
V 1001 050 A1: Accounts Payable Accruals by Location
V 5001 004 *HEX
SELECT
ALL SUM(T01.OQTY09), SUM(T01.TQTY09), SUM((TQTY09*ORDP09)),
SUM((TQTY09*SCSU60))
FROM AULT2F1/PMP09 T01,
AULT2F1/INP35 T03,
AULT2F1/INP60 T02
WHERE CONO09 = CONO35
AND ITEM09 = PNUM35
AND CONO09 = CONO60
AND ITEM09 = PNUM60
AND WHSE09 = STRC60
AND( CONO09 = &CONO
AND GRNO09 0
AND INVD09 0
AND INVD09
IMO you can not use same logic between QRYDFN and QMQRY. IE you have to rework all your QRYDFN to make QMQRY.
You have probably a better choice to build complete sql “on the fly” and run them directly in your RPG code or your CL code (with some tools like EXECUTESQL, see http://jplamontre.free.fr/jpltools.htm)
The same logic can be used in Query/400 or QM queries. The first problem is trying to use the long obsolete Query/400 in the first place. It shouldn’t have been used for more than the past 15 years.
The LEFT JOIN is actually LEFT OUTER JOIN but the word “OUTER” is optional. The same SELECT statement will give the same result in a QM query as it would embedded in RPG, but it will be far easier in QM.
Tom