Hi,

I am having a problem getting the following SQL Select to work. I will present two cases where one works and the second one does not.

select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
      ON TABLE1.PART_NO = C_USED.PART_NO

The above SQL Select gets a query that has a column USED_QUANITY with all different values and specifically some with value of 0 (zero).

But if I change the query to the following (adding AND USED_QUANTITY < 10) at the end, the query has no records with USED_QUANITY of 0:

select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
      ON TABLE1.PART_NO = C_USED.PART_NO AND USED_QUANTITY < 10

Why does the second SQL Select not selecting records of 0 quantity?