>>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 tried your suggestion but I get error that column "USED_QUANTITY is not found." If I remove the expression "AND USED_QUANTITY LT 10" from the SQL Select, the resulting query indeed has the column USED_QUANTITY. So my quess is that when VFP runs the SQL Select it does not "see" column USED_QUANTITY which is created as a result of the SQL Command. Do I undestand the problem correctly?

Looks like WHERE is executed before SELECT list is build.
You can move condition into HAVING clause

      ON TABLE1.PART_NO = C_USED.PART_NO 
  HAVING USED_QUANTITY < 10

Or you can change 'USED_QUANTITY < 10' to

(QTSum IS NULL OR QTSum < 10)