>>Hi,
>>
>>I can't seem to understand how to deal with the following issue.
>>
>>I have a SQL Select that uses derived table that creates a column, e.g. TOT_QTY. If there are no records in the LEFT JOIN (derived table) the value set to TOT_QTY is null. So in the SQL Select I use NVL( TOT_QTY, 0 ). And when I look at the resulting query I see that the records from the derived table are included with the value assigned to TOT_QTY as 0. So far so good. But if I include this column name in the WHERE clause (e.g. WHERE TOT_QTY > 0) none of the records are included in the query. It looks like the WHERE always uses the NULL value of the TOT_QTY and not the one that has been "processed" by NVL(). I hope my explanation is clear. What can I do to make the WHERE work in this case? TIA.
>
>Can you move the condition into the HAVING clause?
No, in this case HAVING would not help. But I found very simple solution (don't know why I have not thought about it before). I use NVL() in the WHERE and this way I can filter it out. Thank you for your suggestion.
"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