Mind The Gap in APEX graph : nonexistent values in time series and model clause

As always, it seemed so easy at the beginning. Just  graph a table on APEX.  Nobody told about the gaps in the data 🙂

To make the case simple , lets create a table with 3 columns. Name ,date and value:

CREATE TABLE TBL
(
 NM VARCHAR2(10 BYTE),
 DT DATE,
 VL NUMBER
);
SET DEFINE OFF;
Insert into TBL (NM, DT, VL) Values ('ali', TO_DATE('13-01-2017', 'DD-MM-YYYY'), 5);
Insert into TBL (NM, DT, VL) Values ('ali', TO_DATE('14-01-2017', 'DD-MM-YYYY'), 3);
Insert into TBL (NM, DT, VL) Values ('veli', TO_DATE('14-01-2017', 'DD-MM-YYYY'), 4);
Insert into TBL (NM, DT, VL) Values ('veli', TO_DATE('16-01-2017', 'DD-MM-YYYY'), 6);
COMMIT;

Select * from tbl;

NM	DT	VL
ali	13.1.2017	5
ali	14.1.2017	3
veli	14.1.2017	4
veli	16.1.2017	6

Up to this point everything seemed ok,   for a human.   But when I tried to graph it on APEX (5.1.0) with the simplest chart object , the result was neither fancy nor true.  Where was the  Jan 16th data and where is the gap in the Jan 15th?

What I have expected was :

Solution ideas was : creating a time dimension either as a table or on the fly.  Dates were easy , but what about the name dimension? I had no clue about which names would pop up in the future.

After some investigation I became aware of the model clause .  To be honest , I couldn’t figure out all the features and powers of it, but found out an example to solve my issue:

select * from TBL
model partition by (nm)
  dimension by (dt)
  measures(vl)
  ignore nav
  rules sequential order(
  vl[for dt from to_date('13-01-2017','DD-MM-YYYY')
  to to_date('16-01-2017','DD-MM-YYYY')
  increment 1 ] = vl[cv(dt)]
 )
order by nm, dt;

M	DT	VL
ali	13.1.2017	5
ali	14.1.2017	3
ali	15.1.2017	0
ali	16.1.2017	0
veli	13.1.2017	0
veli	14.1.2017	4
veli	15.1.2017	0
veli	16.1.2017	6

It partitions the data via “name”, creates the dimension “date” and defined “value” as the measure. IGNORE NAV does the trick of putting zeros for nonexistent values. At the rule phase I define the date period and CV(DT) gets the value of the cell and puts it exactly into the same cell if it exists , otherwise IGNORE NAV fills the zeros.

If there is a better solution in SQL or APEX, feel free to comment
Db = 12.1.0.2 ; APEX = 5.1.0

Update:

A better solution came from @StewAshton by using partiton joins.

WITH v1 AS
 (SELECT TO_DATE('12/01/17', 'DD/MM/YY') + rownum time_id FROM all_objects
WHERE rownum <= 4 )
SELECT nm, time_id, nvl(vl,0) quantity
FROM
 (SELECT nm, v1.time_id, vl
 FROM tbl PARTITION BY (nm)
 RIGHT OUTER JOIN v1
 ON (v1.time_id = tbl.dt))
ORDER BY 1,2;

For more info check  oracle_doc