Difference between QM Query and traditional query
When both a QM Query and a traditional Query are run against a database file with IDENTICAL record selection, the traditional query returns more records. I can only guess that it has to do with a difference in the way each utility handles uncommitted data. It almost looks as if traditional Query "sees" uncommitted changes whereas Query Manager query does not. Can anyone verify this or offer more info on what's happening under the covers. It is a bit unsettling to see different data sets returned from two different i tools given the same selection criteria. Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
September 29, 2010 7:01 PM
UPDATED:
September 29, 2010 7:35 PM
Answer Wiki:
An important difference in joining files exists between the two queries that just dawned on me. This was the cause of the QM query returning different data:
Where the traditional query is set up to join "matched records with primary file", the QM query was defined as just a JOIN. When I changed the QM source to include a LEFT JOIN and moved the JOIN conditions up into the "-- Tables" area, the join conditions between the queries matched and a run of each now returns equivalent data.
Thanks for looking. Any additional tips are welcome!
<b>QM BEFORE:</b>
<pre>
SELECT
-- Columns
C.TEXT AS CONTACT, DATE(DIGITS(WBODT)) AS ORD_DATE, A."TIME", A.cust,
A.SQNBR, A.ORDCN, A.ITNBR, A.BLC, A.BPN, A.DESC, A.QTYO, A.UNETB,
QTYO * UNETB AS ENET, A.DFCTB, A.VDP, B.NSCD, B.QTYCS, MBAL - QTYCS
AS QTYW
-- Tables
FROM "LIBx"/"file1" A,
"LIBx"/"file2" B,
"LIBy"/"file3" C
-- Row Selection
WHERE ((WBODT = &DATEIN)
AND (PROJECT = 'PROJ1')
AND (FIELD = 'WUSER'))
-- Join Conditions
AND (A.cust = B.cust)
AND (A.BLC = B.STKLC)
AND (A.BPN = B.STKPN) </pre>
<b>QM AFTER:</b>
<pre>
SELECT
-- Columns
C.TEXT AS CONTACT, DATE(DIGITS(WBODT)) AS ORD_DATE, A."TIME", A.cust,
A.SQNBR, A.ORDCN, A.ITNBR, A.BLC, A.BPN, A.DESC, A.QTYO, A.UNETB,
QTYO * UNETB AS ENET, A.DFCTB, A.VDP, B.NSCD, B.QTYCS, MBAL - QTYCS
AS QTYW
-- Tables
FROM "LIBx"/"file1" A
LEFT JOIN "LIBx"/"file2" B ON
(A.cust = B.cust)
AND (A.BLC = B.STKLC)
AND (A.BPN = B.STKPN)
JOIN "LIBy"/"file3" C ON A.CD5 = C."VALUE"
-- Row Selection
WHERE (WBODT = &DATEIN AND PROJECT = 'PROJ1' AND FIELD = 'WUSER')
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
