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

I have a query with CTE and I want 0 for the NULL rows.

I want to why COALESCE(tsting), NVL(tsting2) and CASE(rw_cnt) is not working? Am i missing anything?

with dte AS(Select store, date_id from store cross JOIN (SELECT 
(TRUNC(sysdate)-1 + (LEVEL-10)) AS DATE_ID
FROM DUAL connect by level <=( (sysdate-2)-(sysdate-10)))
store<10)

, tmp as(Select calendar_dt, str, CASE WHEN rw_cnt IS NULL THEN 0 ELSE rw_cnt 
END rw_cnt, COALESCE(rw_cnt, 0) tsting, NVL(rw_cnt, 0 ) tsting2
from (SELECT calendar_dt, str,  count(*) rw_cnt FROM table2 group by calendar_dt, str))

Select store, date_id, rw_cnt, case when rw_cnt IS NULL THEN 0 ELSE rw_cnt END testing,
tsting, tsting2 from dte LEFT OUTER JOIN tmp ON dte.date_id = tmp.calendar_dt 
AND dte.store = temp.store
order by store, date_id;

And I get data like below:

+-------+-----------+--------+---------+--------+---------+
| STORE | DATE_ID   | RW_CNT | TESTING | TSTING | TSTING2 |
+-------+-----------+--------+---------+--------+---------+
| 3     | 18-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+
| 3     | 19-OCT-20 | 73     | 73      | 73     | 73      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 20-OCT-20 | 88     | 88      | 88     | 88      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 21-OCT-20 | 63     | 63      | 63     | 63      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 22-OCT-20 | 100    | 100     | 100    | 100     |
+-------+-----------+--------+---------+--------+---------+
| 3     | 23-OCT-20 | 105    | 105     | 105    | 105     |
+-------+-----------+--------+---------+--------+---------+
| 3     | 24-OCT-20 | 36     | 36      | 36     | 36      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 25-OCT-20 | 3      | 3       | 3      | 3       |
+-------+-----------+--------+---------+--------+---------+
| 4     | 18-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+
| 4     | 19-OCT-20 | 30     | 30      | 30     | 30      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 20-OCT-20 | 24     | 24      | 24     | 24      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 21-OCT-20 | 38     | 38      | 38     | 38      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 22-OCT-20 | 21     | 21      | 21     | 21      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 23-OCT-20 | 37     | 37      | 37     | 37      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 24-OCT-20 | 3      | 3       | 3      | 3       |
+-------+-----------+--------+---------+--------+---------+
| 4     | 25-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+

Any suggestion for the query optimization is welcome… 🙂

Answer

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.