>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?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
