Using NVL to Pass Zero's for NULL Values Dynamically

Question

I have the following Oracle SQL:

with dat as
  (
    SELECT 
      (trunc(sysdate) - level + 1)AS ISSUE_DATE,
      'Tesla' AS MAKE
    FROM
    DUAL    
    CONNECT BY LEVEL <= (to_date(sysdate+59,'DD-MM-YYYY') - to_date(sysdate,'DD-MM-YYYY') + 1)
    union
    SELECT 
      (trunc(sysdate) - level + 1)AS ISSUE_DATE,
      'Subaru' AS MAKE
    FROM
    DUAL   
    CONNECT BY LEVEL <= (to_date(sysdate+59,'DD-MM-YYYY') - to_date(sysdate,'DD-MM-YYYY') + 1 )
  ),
  
  
cars as
(
SELECT 
    trim(c.description) MAKE,
    trunc(t.issue_date) ISSUE_DATE,
    count(t.car_id) CNT
FROM 
    cars 
group by
    c.description,
    trunc(t.issue_date)
  )
  
select 
  d.issue_date,
  c.description,
  c.cnt
from
  dat d left join cars c
  on d.issue_date = c.issue_date
  and d.make = c.make
order by
  d.issue_date,
  c.description,
  c.cnt
  

Which returns the following tables:

A header	Another header	CNT
Tesla	2024-03-15	2
Subaru	2024-03-15	3
Subaru	2024-03-14	11
Tesla	2024-03-13	10
Subaru	2024-03-13	6
Tesla	2024-03-12	8
Subaru	2024-03-12	12
Tesla	2024-03-11	17
Subaru	2024-03-11	4
Tesla	2024-03-10	6
Subaru	2024-03-10	9

The table is missing a value for 'Tesla' on 2024-03-14 because the cars table doesn't return a value as there were NULL CNT for that day. I know I need to force the date and count for those dates where a null value is returned but I am not quite sure how to do that in this situation. I have been playing around with the NVL function i.e:

NVL(column, 0)

But because of the way I am creating a dynamic list of 'MAKES' and 'ISSUE_DATE' in the 'DAT' table and left joining it up to the cars table, I am not sure how to pass NVL into this query to force a date to occur and a 0 for the count

Answer 1

This is a problem easily solved with a partitioned outer join (a really good tutorial of which can be found here):

WITH cars AS (SELECT 'Tesla' MAKE, to_date('2024-03-15', 'yyyy-mm-dd') issue_date, 2 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-15', 'yyyy-mm-dd') issue_date, 3 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-14', 'yyyy-mm-dd') issue_date, 11 cnt FROM dual UNION ALL
              SELECT 'Tesla' MAKE, to_date('2024-03-13', 'yyyy-mm-dd') issue_date, 10 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-13', 'yyyy-mm-dd') issue_date, 6 cnt FROM dual UNION ALL
              SELECT 'Tesla' MAKE, to_date('2024-03-12', 'yyyy-mm-dd') issue_date, 8 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-12', 'yyyy-mm-dd') issue_date, 12 cnt FROM dual UNION ALL
              SELECT 'Tesla' MAKE, to_date('2024-03-11', 'yyyy-mm-dd') issue_date, 17 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-11', 'yyyy-mm-dd') issue_date, 4 cnt FROM dual UNION ALL
              SELECT 'Tesla' MAKE, to_date('2024-03-10', 'yyyy-mm-dd') issue_date, 6 cnt FROM dual UNION ALL
              SELECT 'Subaru' MAKE, to_date('2024-03-10', 'yyyy-mm-dd') issue_date, 9 cnt FROM dual),
      dts AS (SELECT  trunc(sysdate) - level + 1 issue_date
              FROM    dual
              CONNECT BY LEVEL <= 7)
SELECT cars.make,
       dts.issue_date,
       NVL(cars.cnt, 0) cnt
FROM   dts
       LEFT OUTER JOIN cars PARTITION BY (cars.make) ON dts.issue_date = cars.issue_date
ORDER BY dts.issue_date DESC,
         cars.make DESC;

db<>fiddle of it working

Answer 2

There are a few problems with your code.

1. It could not result with the dataset having columns "A header", "Another header" and "CNT" - selection list is: issue_date, description and cnt
2. Your WITH clause would cause "ORA-32039: recursive WITH clause must have column alias list" error (cte cars selecting FROM cars)
3. A date plus/minus integer is a date too, don't use To_Date() function that converts date into a date "to_date(sysdate+59,'DD-MM-YYYY')"
4. Your object cars is unknown and we don't know it's description or content - we could just be guessing Please update the question !