Есть следующий запрос
PHP код:
select firstonly inventSum
index hint ClosedItemDimIdx
order by LastUpdDatePhysical
where !inventSum.closed &&
inventSum.itemId == itemId &&
inventSum.availPhysical > 0
#InventDimJoin(inventSum.inventDimId, inventDim, inventDimCriteria, inventDimParm)
notexists join WMSLocation
where WMSLocation.inventLocationId == inventDim.inventLocationId &&
WMSLocation.wMSLocationId == inventDim.wMSLocationId &&
WMSLocation.outputBlockingCauseId;
В итоге генерится вот такой запрос
PHP код:
SELECT A.ITEMID,A.POSTEDQTY,A.POSTEDVALUE,A.DEDUCTED,A.RECEIVED,A.RESERVPHYSICAL,A.RESERVORDERED,A.ONORDER,A.ORDERED,A.QUOTATIONISSUE,A.QUOTATIONRECEIPT,A.DEL_CONFIGID,A.INVENTDIMID,A.CLOSED,A.REGISTERED,A.PICKED,A.AVAILORDERED,A.AVAILPHYSICAL,A.PHYSICALVALUE,A.ARRIVED,A.PHYSICALINVENT,A.CLOSEDQTY,A.LASTUPDDATEPHYSICAL,A.LASTUPDDATEEXPECTED,A.POSTEDVALUESECCUR_RU,A.PHYSICALVALUESECCUR_RU,A.ITEMNAME,A.RECID
FROM INVENTSUM A ,INVENTDIM B
WHERE ((A.DATAAREAID='dat') AND ((((A.CLOSED=0)) AND (A.ITEMID='0000002')) AND (A.AVAILPHYSICAL>0))) AND ((B.DATAAREAID='dat') AND (B.INVENTDIMID=A.INVENTDIMID))
AND NOT EXISTS (SELECT 'x' FROM WMSLOCATION C WHERE ((C.DATAAREAID='dat') AND (((C.INVENTLOCATIONID=B.INVENTLOCATIONID) AND (C.WMSLOCATIONID=B.WMSLOCATIONID)) AND 1=0)))
ORDER BY A.DATAAREAID,A.LASTUPDDATEPHYSICAL OPTION(FAST 2)
Внимание.. вопрос )
А куда делся хинт по индексу
index hint ClosedItemDimIdx
в запросе должно было по идее быть так
PHP код:
FROM INVENTSUM A (index(I_174CLOSEDITEMDIMIDX)),INVENTDIM B