In case where there's conflict between a field name and field alias, the field name wins. IOW, 'USED_QUANTITY < 10' refers to the field from C_USED derived table. You can change a field name in the derived table so it wouldn't conflict with field alias.

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

BTW, you don't need NVL() in the derived table because SUM() never returns NULL when used in a query with GROUP BY.

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