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

Updated after comments

-- Sample Data:
Create Table CARS(MAKE VarChar2(32), ISSUE_DATE DATE, CNT Number(6));

Insert Into CARS 
           ( SELECT 'Subaru', to_date('2024-03-15', 'yyyy-mm-dd'), 3 FROM Dual UNION ALL
             SELECT 'Tesla', to_date('2024-03-13', 'yyyy-mm-dd'), 10 FROM Dual );

There are a few problems with your code.

1. It could not result with the dataset having columns "A header", "Another header" and "CNT" - YOUR selection list is: issue_date, description and cnt

Select MAKE, ISSUE_DATE, CNT From CARS;                         --  without column aliases
/*    R e s u l t :
MAKE                             ISSUE_DATE        CNT      <-----  without column aliases
-------------------------------- ---------- ----------
Subaru                           15.03.24            3
Tesla                            13.03.24           10  */

Select MAKE "A header", ISSUE_DATE "Another header", CNT From CARS;    --  WITH column aliases
/*   R e s u l t :
A header                         Another header         CNT    <------     WITH column aliases
-------------------------------- --------------- ----------
Subaru                           15.03.24                 3
Tesla                            13.03.24                10  */

2. Your WITH clause would cause "ORA-32039: recursive WITH clause must have column alias list" error (cte cars selecting FROM cars)

Select BANNER_FULL From V$VERSION;
WITH
    cars as    -- naming a cte as an existing table
        ( SELECT 
              trim(c.description) MAKE,
              trunc(t.issue_date) ISSUE_DATE,
              count(t.car_id) CNT
          FROM 
              cars   -- existing table
          group by
              c.description,
              trunc(t.issue_date)
        )
Select * From cars;  -- If you where Oracle would you select from table or from cte?

/*      R e s u l t :
BANNER_FULL                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Error starting at line : 2 in command -
WITH
    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 * From cars
Error at Command Line : 9 Column : 15
Error report -
SQL Error: ORA-32039: recursive WITH clause must have column alias list
32039. 00000 -  "recursive WITH clause must have column alias list"
*Cause:    A WITH clause query referred to itself (recursive) but did
           not have a column alias list specified for it.
*Action:   Add a column alias list for the WITH clause query name.      */

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')"

--    Boneist explained it in comments (Thank you Boneist)

4. Your object cars is unknown and we don't know it's description or content - we could just be guessing

-- This is a fact - (excelent) guessing  is what solved your problem

Please update the question !

Above mentioned issues with your code are refering just to those most important - there are more of them - not mentioned yet cause they are irrelevant unless the mentioned ones are solved.

Answer 2

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