mercredi 18 mai 2016

trying to avoid In operator sql2005

I am trying to avoid In operator in the below query, I didn't see any room for adding exist operator. Is there any way to improvise the query

     UPDATE ITEM
    SET QTYREQ=ISNULL(( SELECT SUM( D.QTYORDERED-D.QTYPICKED)
    FROM ORDRDET D, ORDRHDR H
    WHERE D.ASCITEMID=ITEMQTY.ASCITEMID
    AND H.ORDERNUMBER=D.ORDERNUMBER
     AND D.ORDERFILLED IN ('O', 'T')
     AND H.CONSOLIDATED_ORDERNUMBER IS NULL
      AND D.QTYPICKED<D.QTYORDERED
      AND H.PICKSTATUS IN ('N','I','W', 'H', '')),0)
      +(  SELECT ISNULL( SUM( WD1.QTY-WD1.QTY_PICKED),0)
       FROM WO_DET WD1, WO_HDR WH1
       WHERE WD1.WORKORDER_ID=WH1.WORKORDER_ID AND
        ITEM.ASCITEMID=WD1.COMP_ASCITEMID
         AND WD1.QTY_PICKED<WD1.QTY
         AND WH1.STATUS IN ('S','N'))

Aucun commentaire:

Enregistrer un commentaire