>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?

In this case it's not clear which Used_Quantity you're referring to.

Try

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 (c_used.USED_QUANTITY < 10 or c_Used.Used_Quantity IS NULL)