>>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)
--sb--