>>Hi,
>>
>>I am having a problem getting the following SQL Select to work. I will present two cases where one works and the second one does not.
>>
>>

>>select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
>>  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
>>      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
>>      ON TABLE1.PART_NO = C_USED.PART_NO
>>

>>
>>The above SQL Select gets a query that has a column USED_QUANITY with all different values and specifically some with value of 0 (zero).
>>
>>But if I change the query to the following (adding AND USED_QUANTITY < 10) at the end, the query has no records with USED_QUANITY of 0:
>>
>>

>>select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
>>  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
>>      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
>>      ON TABLE1.PART_NO = C_USED.PART_NO AND USED_QUANTITY < 10
>>

>>
>>Why does the second SQL Select not selecting records of 0 quantity?
>
>In this case it's not clear which Used_Quantity you're referring to.
>
>Try
>

>select TABLE1.*, NVL(C_USED.USED_QUANTITY,0) AS USED_QUANTITY FROM TABLE1 
>  LEFT JOIN (select TABLE2.PART_NO, NVL(SUM(TABLE2.USED_QTY),0) as USED_QUANTITY, 
>      FROM TABLE2 GROUP BY TABLE2.PART_NO) C_USED 
>      ON TABLE1.PART_NO = C_USED.PART_NO 
>AND (c_used.USED_QUANTITY < 10 or c_Used.Used_Quantity IS NULL)
>

I thought about it too (the fact that the name could be confusing). But somehow, it does select all records with QTY > 0 but not equal to 0. But I will try your suggestion too.

Thank you.