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