coalesce, nvl and CASE not working for count(*) in the subquery

You are doing a LEFT join of dte to tmp, so you get nulls for the unmatched rows.

Maybe you think that by using COALESCE() and NVL() inside tmp the final results will show 0 instead of null, but this is not right.
COALESCE() and NVL() are internal in tmp and if you select from tmp only then you will not get nulls, but since you do that LEFT join and there are unmatched rows then the columns for these unmatched rows will be represented by nulls.

So you have to use COALESCE() and/or NVL() in your final SELECT statement if you want to remove these nulls.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top