>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.
"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
