Same query different results why?

Tools & Utilities
Enthusiast

Same query different results why?

I ran the query @ 10 am and received 386 records...
I ran the same query (no changes) @ 10:23 and received 8,691 records...

What could cause this phenomenon??? Here is the query
If the table/view is being loaded by the system and I try to use it, my program should wait correct? why would it return two different results

Select VORD.ORDER_NO,
VORD.BTN_NPA || VORD.BTN_NXX || VORD.BTN_Line_NO AS BTN , VORD.Sales_Rep_ID,
VORD.MOD_DT_TM,
VORD.TYPE_CD,
VSOSIREF.SRC_STS_IND_E_DESC
From VORD ,
VBCHPROD,
VORDMTR,
VORDITM,
VORDITMD ,
VSOSIREF
Where VORD.EVENT_ID = VORDITM.EVENT_ID
AND VORDITM.ITEM_ID = VORDITMD.ITEM_ID
AND VORDMTR.EVENT_ID = VORD.EVENT_ID
AND VORDITMD.PRODUCT_ID = VBCHPROD.PRODUCT_ID
AND VORD.STATUS_IND = VSOSIREF.STATUS_IND
AND VORD.SRC_CO_CD = VSOSIREF.SRC_CO_CD
AND MOD_DT_TM > '2006/08/11 00:00:00'
AND MOD_DT_TM < '2006/08/18 00:00:00'
AND VORD.sRC_CO_CD = '06'
AND VORD.so_region_cd in ('1','2')
AND PRODUCT_E_DESC = 'BID'
AND VORD.STATUS_IND IN ( '34', '29')

1 REPLY
Highlighted
Enthusiast

Re: Same query different results why?

Your query will wait only if blocked by a "write" lock on one of the tables. Depending on the method being used to update the data (i.e. Tpump, Multiload, etc.), you may or may not be locked out of the entire table. Some utilities, such as Tpump, lock at the row level, so your read lock would be able to eventually get in to read the table (in fact, your read lock will probably be blocking the updates in this case).

Another possibility is if you are referencing views in your query, rather than tables, the views could have a "LOCKING ROW FOR ACCESS" or similar access-locking clause in the view. If this is the case, then your query will not wait on the update process, even if the update process has a write lock on the table. It will simply read the un-committed data as is.

A third possibility is that one of the views utilizes one of the OLAP functions and the code that references the OLAP function is not uniquely identifying each row, thereby allowing the function to randomly pick a different row or rows each time it executes. I have seen this happen many times when an OLAP function is being used to pick a row (the row with the maximum combination of fields, for instance).