title: how to make sql select with subqueries faster
body: <p>I have a complex SQL select statement. It is very poor in performance. I was wondering how I can make it faster.</p>
  
  <pre><code>SELECT 
       ............
       ............
       CASE WHEN (SELECT SUM(T.ONHAND) - SUM(T.RESERVED) FROM LV_001_01_STINVTOT T WHERE STOCKREF = S.LOGICALREF AND T.INVENNO IN (13,4)) &lt; 0 THEN 0
            WHEN (SELECT SUM(T.ONHAND) - SUM(T.RESERVED) FROM LV_001_01_STINVTOT T WHERE STOCKREF = S.LOGICALREF AND T.INVENNO IN (13,4)) &gt;= 0 
              THEN  (SELECT SUM(T.ONHAND) - SUM(T.RESERVED) FROM LV_001_01_STINVTOT T WHERE STOCKREF = S.LOGICALREF AND T.INVENNO IN (13,4))
       END AS MIKTAR
       ............
       ............
  FROM XXX AS S
  </code></pre>
  
  <p>In the CASE above, I know I had used the same subquery 3 times and makes the whole script too slow.</p>
  
  <p>How can I make it faster?</p>