>>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?
I think it's better to not use newly created columns at all, only in the form of derived tables. SQL Server would not be able to access such columns either, only in ORDER BY clause.
If it's not broken, fix it until it is.
My Blog