>>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.
>>
>>>
>
>Thank you. I understand now. And I will change the field name. Regarding the SUM() and NULL I didn't know that either. Thank you; this will simplify my expression.

You're welcome.